In excel change color of a range of cells based on a cell valueBased on a cell value from a drop down box, how can you make a range of cells
to change background color?
Use the cell the box is linked to in your conditional formatting formula.
If your box is tied to, say, A1, make sure the conditional formula is like:
=$A$1="value"
value is whatever will be selected in the box.
Then set the background color.
Squeaky
"kwitt" wrote:
> Based on a cell value from a drop down box, how can you make a range of cells
> to change background color?
...
How to perform sum sum sum...How to perform sumation within that particular item but the item is not
unique...means item 1 has its own quantity and same goes to item 2...but in
the same table...
--
Message posted via http://www.accessmonster.com
On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com"
<u33296@uwe> wrote:
>How to perform sumation within that particular item but the item is not
>unique...means item 1 has its own quantity and same goes to item 2...but in
>the same table...
Group By the item.
For a more detailed answer, please post a more detailed question (with a
des...
Multiple variables to sort and sum, return values<0 with sum referCan anyone help me with this one please? I have this table of data. I'd like
to be able to write a formula(s) which sums the No according to date and
code, but then only returns a sum value (with the code and date in the two
adjacent cells) if there is a value greater than 0.
Code Ref No Date
1 G/032/05/999 400600 212 19/03/2010
2 G/032/03/001/999 400500 50 19/03/2010
3 G/032/02/001/001 400400 170 19/03/2010
4 G/032/05/999 400600 315 19/03/2010
5 G/032/03/001/999 400500 300 19/03/2010
6 G/032/05/999 400600 202 19/03/2010
7 G/032/03/001/002 40...
how to find out codeHello,
My MFC app crashed in module msvcrt.dll at 001b:77c37098. What can I do to
find out what is on that address?
Thank you
Vaclav
> My MFC app crashed in module msvcrt.dll at 001b:77c37098. What can I do to
> find out what is on that address?
>
Its likely because something wrong was passed to it which caused it.
Typically when you debug you will be able to pinpoint the cause as you
will have the stack to see what parameters you are passing etc. Debug
the app and see if you can reproduce it.
---
Ajay
It happened on the customer's PC. The app generated a crash dump and...
Conditional formatting #50Hi,
The worksheet I've been working on uses paste links to carry forward
workers details from one worksheet to the next. These cells are left
unprotected so the chain can be broken if a person leaves, and someone
new has to be inserted.
This is fine but it makes it tricky to spot where breaks occur. Can
conditional formatting tell the difference between a formula and text
entered? If so can someone please give me an example that I can use.
Many thanks.
DonH
You would need to build a simple UDF
Function IsFormula(rng as range)
IsFormula = rng.HasFormula
End Function
and use t...
Make over �1000 by investing only �5 FIND OUT HOW HERE WONT HURT TO LOOKMake Money with PayPal
by "beatlerockin" <beatlerockin@[EMAIL PROTECTED] > May 11, 2004 at 11:54
AM
Follow the directions below and in two weeks you'll have up to $2000.00 in
your PayPal account. There is a very high rate of participation in the
program because of its low investment and high rate of return. Just $5.00
to
one person!
THAT'S ALL !!!
If you are a skeptic and don't think the program will work, I urge you to
give it a try anyway! It REALLY WORKS! Why do you think so many people are
promoting this?
LOOK AT IT THIS WAY If the Program is a total fail...
Date-range specific report with counted results of combo box selectionsHi there,
I'm a novice with Access, but am currently setting up a patient
database for an NGO in India that I'm volunteering with. The primary
reason for the database is to collect statistics quarterly. It is a
simple database with one table that collects patient data from simple
forms with some fields having combo boxes.
Ultimately I want to generate reports that will show a summary of
'counts' of various fields for a given period. For example:
In the table I have a combo box for where the person came from, with
several options including - "Pick up Y. Bazar", &quo...
Dates from Julian calendar
I know it can be done - just don't know how!
Embedded into barcodes we use the Julian date system. I need people to
enter the julian date into the spreadsheet and the adjacent cell to
display the date.
Excel seems to give every possible way to convert to Julian dates but
not the other way round
Can anyone please help this numpty?
Thanks
Tony
--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947
View this thread: http://www.excelforum.com/showthread.php?threadid=47...
nth cell in the rangeWithin a VBA Module, I would like to set the ith component of my array
A(1 to N) to be equal to the 100*(nth cell in a column in excel).
How can I do this within a loop?
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
Hi
try something like the following
sub foo()
dim c_values(1 to 100)
dim i
for i = 1 to 100
c_values(i)=activesheet.cells(i*100,"A").value
next
end sub
>-----Original Message-----
>Within a VBA Module, I would like to set the ith
component of my array
>...
Excel find gives exceptionHi,
I get a Range object from an Excel worksheet object in VC++.
I use .olb files to import the tlbs.
I have a problem with Find() method of the Range object.
I get a "scalar delteing destructor" exception and get a HRESULT
of -2146827284.This happenes only for the 2nd parameter of the Find().
VARIANT WhatVt;
WhatVt.vt = VT_BSTR;
WhatVt.bstrVal = ::SysAllocString(L"PATIENT");
VARIANT AfterVt;
VariantInit(&AfterVt);
AfterVt.vt = VT_BSTR;
AfterVt.bstrVal = ::SysAllocString(L"A1");
VARIANT LookInVt;
LookInVt.vt = VT_ERROR;
Loo...
Summing up user defined resultsHi all - I'm new to VBA programming in Excel and so any help i'd
totally love!
I'm currently writing a function.
Objective: user can select rows (do not have to be sequential). User
clicks on button. UserForm appears with summed results from ONLY rows
that he selected.
What I have now, well it doesn't work:
Sub Button6_Click()
Dim i As Integer
Dim totalNumbers As Integer
Dim aRange As range
For Each a In Selection.Areas
'MsgBox "Area " & i & " of the selection contains " & _
' a.Rows.Count & " rows."
&...
sum subform to form=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])
Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a
field named Pledge Amount. I want to put a field on the form that gives me a
total of the subform's Amount Pledged. I have tried the above, but I get an
#error in the unbound text box.
Do you need any more info? Any thoughts on why this does not work?
Thanks in advance,
Scott
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1
The proper syntax is
=Sum([Forms]![frmCa...
Calculate sum of a field within the queryI have a query that returns the details for a failure record during a
specified time period. Where if I search between 1/1/08 and 1/31/08 the query
returns the records of all failure records during that period. The row shows
the part number and the total parts failed for that record.
I need the query to then sum the total part failures for the previous 12
months. So I would see the following:
Record # | part # | parts failed | Origination Date| Total Parts failed past
12 months
44444 | x | 4 | 1/2/08 | 200
44445 | Y |1 ...
Automatic filling of cells within a certain date rangeCan someone please help, I have been stuck on this for the past two days...
Column 'AB' has 'Start Date' and column 'AD' has 'Finish date'
Row 19, cells 'AI' to 'DW' have the months written as 'J' 'F' 'M' 'A'
etc.....from 2008 to 2015.
I want to be able to enter a 'start date' in column 'AB' i.e. 1 January 2008
and a 'Finish date' in column 'AD' i.e. 1 April 2015. Then i would like the
cells 'J - A' on row 19 to be filled with '1' in each month....
I...
using intersection of rangesNeed to specify the intersection of two named ranges, but
require either one or both of them to be referenced to
other cells. In other words, someone would enter the
range which is one or both of the two, and I need the
formula to look at that cell and use its name in the
intersection argument.
Any ideas? I have tried TEXT, but that doesn't seem to do
it.
Suppose the rangenames are rangeA and rangeB, and the names are in D2 and D3
=INDIRECT(D2) INDIRECT(D3)
Note the spaces between the two functions; space is the intersection
operator.
Now you'll have to test for one or two...
how to make this work if sum=5+n2 then sum becomes the value of s.I want to get or create a formula in excel or access that allows me to keep a
running total of my supplies ie... I have 2 pens, remove one and receive 2.
answer in the cell becomes 3 pens then if I zero out the received cell the
inventory cell still remembers that I have 3 pens not 4 pens (because I
received 2 more pens) and visa versa when I zero out the received cell it
remembers I have 3 pens not 1 pen(because I removed 1 pen)
I believe the method you suggest is flawed. You have no audit trail. If the
number on the sheet doesn't match your actual inventory, how will you figure
out...
Read and findHi.
I need a procedure to read a name in Document1 then go and find it in
Document2. It then pauses for 10 seconds before returning to Document1 and
reads the next name below. This is repeated until the name is a blank. Any
help is much appreciated.
TIA
Tom
First, let's get the nomenclature.
A file is called a workbook
A worksheet within a file is called a worksheet or sheet. Sometimes tab..
Why the pause?
What you may want is a FINDNEXT macro (look in the vba help index) with a
msgbox ?? If all else fails,
If desired, send your file to my address below. ...
=SUM Ranges Do Not UpdateI have a Excel 2000 spread sheet, with the following macro to insert
new row.
Sub InsertRow()
'
' Macro1 Macro
' Macro recorded 4/27/2004
'
'GoTo label, MyString
ActiveSheet.Unprotect
Application.Goto Reference:="MyCell"
ActiveCell.Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveCell.Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True
End Sub
The problem is that in the "Mycell&qu...
Date rangesHi
I have a list of predefined dates in column B in colum C formula to show
todays date. (i.e. if today = 24-6-06)
What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06)
A B C D
1 1 12-6-06 =today() next date =
2 2 19-7-06 previous date =
3 3 24-8-06
4 4 30-9-06
Any help appreciated.
Regards
Paul T
Paul T wrote:
> I have a list of predefined dates in column B in colum C formula to show
> todays date. (i.e. if today = 24-6-06)
> What I want is D1 to to show B2 (19-7-06) an...
Sum value between dates problem driving me mad!Good Evening All,
I am really struggling here, have tried, nested IF's including AND's but am
a bit stumped.
I am fairly familiar with Arrays, but cannot seem to combine all.
I have a data table (as detailed below). (Hopefully, this should be
'pastable' into Excel - it works for me)
I simply wish to calculate the expected totals per month.
Any help & assistance with this would be most welcome.
Cheers,
Mathew
Note, earliest Start is 01/04/03
Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003
01/04/2003 23.00
01/04/2003 09/07/2003 23.00
...
Conditional Formating #24Hi,
I have a list of data. It is 4 columns by 100 rows. Cells A2:D102
If the last column in the row is = "D" I want to highlight that row.
So for example
If Cell D2 = "D" then highlight Cells A2:D2
Is there any way to do this using conditional formating, I was trying but it
would not highlight the other cells - I could highlight cell D2, but not
cells A2:C2, based on the value of cell D2.
In the conditional formatting box (Formatting->"Conditional Formatting"),
Choose "Formula Is" from the dropdown box, and enter this formula (verbatim)
in ...
Change Text Case to Upper in a cell range A2:A92I have a workbook of Contacts. In column A there are Names. I wish to
convert those to UPPER case. I've seen a few VB codes that change the case
to UPPER if I change something in the cell, GREAT if I want to go through
the entire Column and change something. But is there another way?
"Barry" wrote:
> I have a workbook of Contacts. In column A there are Names. I wish to
> convert those to UPPER case. I've seen a few VB codes that change the case
> to UPPER if I change something in the cell, GREAT if I want to go through
> the entire Column and change s...
Conditional Sum WizardCurrently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.
Can anyone give me a hint on how I can solve these issues? Thanks.
Please don't multi-post - you have an answer elsewhere, relating to
use of CSE.
Pete
On Jan 6, 9:23=A0am, MarcoKoenders
<MarcoKo...
stop all the columns coming up the same length in a chartwhen i try to make a chart in excel all the columns come up the same length
and it won't display the years either
Hi,
Your post is a bit lite on detail so I have more questions than answers.
Is it possible you have selected the 100% stacked column chart?
What data do you have and how is it laid out?
Where should it be displaying the years, axis or as data labels?
Cheers
Andy
prinshin wrote:
> when i try to make a chart in excel all the columns come up the same length
> and it won't display the years either
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
...
Formula for a range of percentagesSorry, some of the posts mention this, but I do not know enough to figure it
out. I am a teacher and I am trying to collect some data. Please help.
I need a formula to count the number of grades that are greater than 74% and
less than 85% in the cell range of H3:H109. Any help would be greatly
appreciated.
I'm sure there's another way, but try this:
=COUNT(H3:H109)-COUNTIF(H3:H109,"<=.74")-COUNTIF(H3:H109,">=.85")
"Tkshowers" wrote:
> Sorry, some of the posts mention this, but I do not know enough to figure it
> out. I am a teacher...