Free Microsoft Excel 2013 Quick Reference

Conditional formating for a cell based on another cells value Results

I am sure this easy but it's driving me mad.

I have cells in a worksheet in workbook A that compares values within it to an identically named sheet in workbook B.

At the moment I am using "helper" cells to identify a difference between the two workbooks/worksheets/cells and then using that helper cell to crive some conditional formatting.

What I want to do is drive the conditional formatting directly.

So what I intend is a cell on the worksheet in workbook B (let's say A1) that contains a filename for workbook A.
The conditional format would need to compare a cell in the current workbook (workbook B) with the same cell in the same named worksheet in workbook A.

Then, as time progresses and I create workbook C, I would change the cell A1 in workbook C so it referred to workbook B.

In case it's not clear, I'm trying to spot and track changes between different versions of a workbook.

The "helper" for comparing cell A8 currently looks something like ...
=IF(A8<>'C:Dir1Dir2[workbook V1.02.xls]worksheetA'!A8,1,0)

I want this in a cond format in A8 to save the use of helper cells (which have a habit of being overwritten etc by other users).

Thanks in advance.

I have a software order sheet that has, in column B, a validation list,
comprising 2 entries, a blank entry and a specific software product
(Product X). That way the users can enter their own software product or
select Product X.

Column E (the monthly rate cell) has been left blank for the user to
enter their own rate, but I am trying to get the Conditional Formatting
to populate the cell with a value if Product X has been selected in
column B.

I have entered the following formula in the Conditional formatting but
get nothing in the monthly rate cell when Product X is selected.

=IF(B8="Product X",VLOOKUP(B8,Software_Rate,2,False),0)

I have tried out the formula "stand-alone" and it works perfectly

I am using the VLookup as the software selection list is certain to
grow.

Can anyone point me in the right direction please.

Regards
Fred

I've used conditional formatting based on a simple formula like =$B6=1. Now
Excel warns me of macro viruses and I have to choose whether or not to
enable macros before opening this spreadsheet. I'm pretty sure this formula
is the cause because I've tested it in a spreadsheet that contains nothing
else except the two cells under test (B6 and the cell I'm formatting). If I
change the formula to a constant and use "cell value is" then the macro
warning goes away.

I certainly haven't created any macros in the workbook and if there were any
presumably the warning wouldn't go away when I remove the formula.

Are formulae treated as macros by Excel security? And is there any way to
achieve conditional formatting off formulae (needed to format one cell on
the basis of the value in another) without incurring security warnings?

While I could change my own security settings I'm not at liberty to change
those of colleagues who are using such a spreadsheet and who don't want the
warnings to appear each time the workbook is opened.

Thanks for any ideas you have.

Kevin Lucas

I have a spreadsheet that contains data in columns A through J.

Each cell under those columns is Conditionally formatted such that if an error check passes against data in another sheet, the cell is Green and if the error check fails, the cell is colored "red".

There are several thousands of rows of data in the sheet.

I'm looking to color a cell, likely the column header either green or red based on all the color values of the cells in that column as an easy indicator for the user to know if there is a problem in their data.

So for example, if all the cells in Column A within the Table are Green, then the column header for Column A would be green. If there was one cell however in Column A that was Red, say A5895, I would want the column header to show as Red indicating that an error condition exists.

Thanks in advance

Hello,
This might be something simple I cannot solve. I am learning excel for work and I ran into a request I cannot make. My boss asked me to make a conditional format that would change a couple cell's colors all at once based on a value of another cell. Example, if one cell reached over 45% a couple others would all change green. Well I got that one without much trouble. Pretty much I got the formatting down for any greater or lesser value to change but not a between. I can't see to find out a way to make a couple cells change color all at once based on a value between 25% - 30%. Any help would be appreciated. Thanks

I am using the MSN MONEY Add-in with Excel 2007. I would like to keep & track historical data and am attempting to do the following:
1) I want the dynamic quote to be pasted on a cell on another sheet as a static value. I think this is =sheetname!cell#
2) I want that same static cell value to highlight purple if it 5% higher or 5% lower than the previous static cell.
3) I want that same static cell value to become red text IF it is less than the original static cell value (the value in the first column)
4) I want that same static cell to become underlined IF it is less than the previous displayed static values. I assume this would be tricky because the number of previous cells could differ. If there are only 4 cells with static values before the cell in question, then it would underline based on those 4 cells. If there are 100 cells with static values before the cell in question, then I would want it to underline based on only the visible cells. When I say visible cells, I am freezing the panes so that I can continue to add data and assume that the maximum number of visible cells would be 10 to 15.
5) Not only would I then want to copy these formulas/formatting to the other stocks, but to make it even more complex, I would like for the above conditions to move one spot to the left after the close of each business day (4:20pm).
Is this possible?
The point of this is to see static historical quotes and be able to see at a glance what stocks are at recent lows, lows since I have been tracking them, and big jumps up or down.
Thanks in advance for any help you can provide.

Hi,

I have a talbe in a sheet with conditional formatting highlighting cells over a certain value (turns out there are 7 highlighted cells). I want to extract the whole row that those highlighted cells are in to a new table in a different sheet.

I've been trying to figure this out for a bit too long now, so here i am. Thanks in advance for what I'm sure is going to be an extremely simple method.

Josh

I am wondering if it is possible to format a cell that contains text - based on a number in the cell next to it. Here is what I have:

Category......Mean
Results.........3.3
Morale..........3.8
Buy-In..........4.0

I would like the word 'Results' to display in BLACK text because the mean score is in the range 3.0-3.9 (same goes for the word 'Morale')
I would like the word 'Buy-In' to disply in GREEN text because the mean score is in the range 4.0-5.0

Basically, the mean scores are calculated using formulas and I would like the color of the category to change based on the mean score number.

Is there a way to use conditional formatting to do this? Or another way to automatically make this work?

File is attached.
Thanks!
Sarah

I need help with my conditional formatting.

I have data from A5 through I56. Columns D,F,and H are the only columns with which the user will enter data. The other columns are eithe labels or calculed cells.

I am trying to write a conditional formatting formula based below, but yet it doesnt seem to work correctly.

if looking at row 5, I want:
=If(D5<>"",1,0)

and if looking at row 6, I want:
=if(D6<>"",1,0)

so on and so worth though the entire range up till row 56.

And if true, then 'grey' out range A through I with grey fill.

My try was to try it for the range of A5 through I5 and write =If(D5<>"",1,0) with my format, and then just copy that rule for the entire range.....but the result is incorrect. When I write this rule and place a value in D5, it greys out A5 through F5, not through I5 as requested.

Sampel worksheet attached. Sheet1 is my tab with data. I also have another tab label INCORRECT OUTPUt and this just shows what happens with my current formula. And I also have a DESIRED OUTPUT to help show what I need.

Thanks for looking at this with me.

I need to format C2 so that it removes all text that comes after a dash in B2, but if no dash exists, then it returns the text: Parent.

For example, here are 3 values in B2, B3, and B4:

1234-s
1234-m
1234

In C2, C3, and C4 I'd like to have the following values returned based on the above values in B:

1234-
1234-
Parent

With the help of ChemistB and MartinDWilson from another thread, I used the following formulas to remove text after the dash:

=LEFT(A1,FIND("-",A1)-1)
or
=LEFT(A1,FIND("-",A1)-1)+0

but I don't know how to add another condition in those formulas to make it return the text of "Parent" if dashes don't exist.

Can someone please help?

Thanks

I am trying to make a graph for work that shows the deviation of a timed process in a row on a spreadsheet. I have a row of cells from H1 to AG1. Cell G1 will have a numeric value that can be anywhere from “0” (zero) to “25” (twenty-five). Based on that number in cell G1, I want to place a colored icon in a particular cell from H1 to AG1. The ranges I am trying to focus on are that cell H1 would represent 0; I1 would represent 1; J1 – 2; K1 – 3; etc. Zero to Three would be “green”, Four to Ten would be “yellow” and, Eleven to Twenty-Five would be “red”. For example: If the number in G1 is “3”, then I would have a round green icon in cell K1; if the number were “4”, then a round yellow icon would be in cell L1; “11” would have a round red icon in cell S1, and so on.
I have toyed around with conditional formatting, but the rules are limiting and my knowledge of formulas is also limited. Any suggestions would be greatly appreciated. Hopefully this is not too confusing to visualize in written form.

After reading this post, I realized that it is not worded properly, which is probably the reason that it has many viewers and no replies. I will attempt to reword this and post it to another site. Thanks, anyway.

Hi guys,

I'm new to this forum and hope I won't be disappointed :-)

So to start with I have a spreadsheet with a few columns.

I have grouped by one of the columns...which looks something like this:

a
a
a
a
b
b
b
b
c
c
c
d
d
e
f

Anyway, what I want to do is to have a different colour everytime the value changes (like i have shows above)...so all a's will be one colour, b's will be another, c's another etc...it will let me see easier where we only have 1, 2 or 3 rows (I expect to see 4). I don't mind if the colour alternates between two colours e.g Red and Yellow.

I can't find what I'm looking for in conditional formatting (maybe I'm not looking hard enough!).

Please help!

Thanks

ps - I am using Excel 2007

Hi there, I'm trying to create some conditional formatting rules for a grade sheet.
I currently have a setup worksheet which has a named range ("grades") which runs through a grading system (8a,8b,8c,7a,7b etc) and assigns each with a numeric value (1 through 22 since there are 22 grades)

Essentially, my conditional format needs to calculate the difference between 2 grades by looking up their numeric value and colouring based on differences I set.

I can successfully calculate the difference between 2 grades on my worksheet by using the following formula:

=(VLOOKUP(I4,grades,2,FALSE))-(VLOOKUP(F4,grades,2,FALSE))

This essentially looks up the numeric value for the 'Target Grade' (I4) and the numeric value for the 'actual grade' (F4) and calculates the difference between them (2 - since the grades are '4c' and '3b', they are within '2 grades' of each other)

When I apply a similar formula in my conditional formatting rules, I add an extra condition with another operator, for example...

=(VLOOKUP($I$3,grades,2,FALSE))-(VLOOKUP($F$3,grades,2,FALSE))>2

The formula gives an error if I remove the $ characters. In essence, the above formula should colour the target cell Red because the student has strayed too far away from their target grade...

I have applied an opposite rule to colour the cell green to check my logic (<=2 at the end) but the cells are not colouring.

My other uncertainty is how to refer to the 'current' cell in the conditional formatting statement. So, something like this...

=(VLOOKUP(CURRENTCELL,grades,2,FALSE))-(VLOOKUP(CURRENTCELL,grades,2,FALSE))>2

Any help would be greatly appreciated. I can script in VB if there is an easier way to do that, I can write in VB.NET but not really looked into excel before like this...

I have a spreadsheet in Excel 2007 I use to project "future values", based on weekly (historical) inputs. Each week I add the last week's numbers, and it updates the projected "future values" column. So the "future values" column changes a little every week.

I also have a set of "milestone" values. For example, the set (500, 750, 1038, 1247, 2563, etc.) offers some "milestone" values for the sake of discussion - I might have fewer or more milestones in a week.

I would like to conditionally format my "future values" column to highlight the single cells that are "closest to/greater than" each of my "milestones".

The conditional format formula should compare the "future values" column against each value in a set of "milestones", and highlight each single cell closest/higher than each of the "milestones". It should answer the question "which week will I meet or break the '500' milestone? Ahh...week 12. The '750' milestone? I see...week 23. The 'xyz' milestone?..." The milestones don't each need to be formatted differently...they could all be blue or whatever, just so they "pop".

I have used the following conditional format formula, however it does not meet all the requirements:

=$B3=((VLOOKUP(750,$B$3:$B$211,1,TRUE)))

where "B" is the future values column, "750" is a milestone, and "TRUE" selects nearest value rather than exact value.

1) it doesn't compare against multiple values...the one value is hard-coded into the formula, so can't be changed on the fly, and
2) it picks the closest/smaller value...not the closest/larger value.

Can someone suggest some syntax that meets my criteria?

If the milestone '750' in the formula could just be substituted with a range like 'Z1:Z10', that would be great...not that easy, apparently.

Good Evening,

I would like to format a cell value based on the date of another cell.

Exemple: I need 5000 on A1 to become red if the date in B2 is below the 31/08/2008.

I have tried to use conditioning fomatting formula, then the IF formula, but I cannot find the right one.

Many thanks in advance for your help

Hi

I have 2 areas where I need help

Help-1:
I need help in "copy" or "move" (but append) a row from a "data" tab if it meets a certain condition into a "new" tab.
I familiar with formula usage but not VBA

Eg:
If tabname=data has rows such as: "^" is indicative of new column for visual only
Index^firstname^lastname^dob^country
1^fn^ln^120811^usa
2^fn2^ln2^081211^canada
....
I'm looking to copy/append indexed row=2 if "firstname=fn2" into a new column, say "output" and output tab should look like
3^fn4^ln3^110811^mexico
2^fn2^ln2^081211^canada

Help-2:
I have frequently used conditional formatting on the cell value itself. However, conditional formatting is not working if the condition being tested is another cell. Eg: Let's say A1="value"; I want C4="test A1=value and if true" and turn GREEN or turn RED if not true. My conditional equation works on A1, itself - but not if I want C4 to format based on A1.

Thx
Rajiv
rajivkohli2011@gmail.com

Hello I would like to format a 3D bar chart based on the following 5 x5 matrix example(Input A ranges from 0% to 20%, Input B -10% to 10%)

Input B
-10.00% -5.00% 0.00% 5.00% 10.00%

0.00% 16% 18% 20% 23% 28%

5.00% 14% 16% 18% 21% 26%

Input A 10.00% 12% 14% 16% 19% 24%

15.00% 10% 12% 14% 17% 22%

20.00% 8% 10% 12% 15% 20.00%

1. The initial bar chart (z value = Output = 20%) associated with initial x/y value (Input A = 0%/Input B = 0%) should be coloured say blue.
2. The bars associated with values for the rest of the matrix should be coloured grey
3.There should be a conditional format such that if say input A = 10% Input B = 5% (which references two cells in another sheet say C10 (input A value) and C11 (input B value) then the bar associated with that value (21%) should be coloured say yellow

Summary: there should be three different coloured bars (initial xy coordinate bar, final xy coordinate bar, and bars associated with the remaining xy coordinates in the matrix)

Steps 1 and 2 are simple to do (e.g. can fix on original chart) but the key point is that the conditional format for step 3 should be able to work so that if the selected value is changed to say input A =15%, Input B = 0% then the new coloured bar should be that associated output value of 14%

It would be ideal if this could be done in a way that such that intermediate values (i.e. not in data matrix) could also be plotted in step 3 e.g. for example plot/colour yellow a value of say input A =12%, Input B = 1% with output value of say 15%. However for the moment happy with a method that is just be based on values in matrix.

Thanks

In Excel 2007, I would like to show an icon based on the value in another cell.

For example:

If B1 > A1, show a green up arrow in B1
If B1 = A1, show a yellow sideways arrow in B1
If B1 < A1, show a red down arrow in B2

And repeat the rule for B2 & A2, B3 & A3, etc.

This seems so simple but the rules available for icon sets seem limited to fixed thresh holds, and absolute references.

What am I missing?

**** VERIFIED: NO SECURE DATA INCLUDED IN SAMPLE ****
UPLOADED IN MULTIPLE PARTS DUE TO FORUM LIMITATIONS ON FILE SIZE
THESE ARE ALL SUPPOSED TO BE IN ONE WORKBOOK

PLEASE HELP...ANYONE...I AM HAVING A LOT OF DIFFICULTY TRYING TO MAKE THIS SPREADSHEET RUN AS SMOOTHLY AS POSSIBLE. I NEEDED TO ADD SOME COLUMNS TO AUTOMATE MUCH OF THE REPETITIVE DATA ENTRY AND THIS CAUSED MUCH OF THE CONDITIONAL FORMATTING TO NOT WORK PROPERLY.

1. I AM NEEDING TO FIX THE BUGS FOR THE COLOR CODING IN COLUMNS ‘J’, ‘M’, ‘P’, ‘Q’, ‘AI’, ‘AJ’, ‘AL’, ‘AO’, ‘AP’ AND ‘AT’

2. I AM NEEDING TO FIX THE BUGS THAT ARE CAUSING THE DOLLAR AMOUNTS TO CHANGE TO RANDOM DATES IN COLUMN ‘AB’
a. THESE ARE TO ADHERE TO THE RULE THAT FIRST THEY WILL DRAW THE COLOR CODING BASED ON TODAY’S DATE (CELL ‘C2’) AS LONG AS THE DATE IN THE COORELATING ROW FOUND IN CELL ‘AS’ IS ‘TBD’ OR BLANK. IF A DATE IS IN THE COORELATING ROW IN COLUMN ‘AS’…THAT DATE WILL SUPERCEED TODAY’S DATE ‘C2’

3. AS WITH THE RULES POPULATING THE ‘SEPT’11 ANALYSIS’, I NEED THE SAME KIND OF RULES TO POPULATE ‘SHORT SALE INCENTIVES’ AND ‘SHORT SALE OFFER ANALYSIS’………..THIS IS PROVING TO BE TRICKY AS THEY ARE CODED WITH MACROS THAT DRAW FROM ‘ORIG LP & LOSS’ AS WELL AS ‘LIST PRICE REDUCTION’. I’M TRYING TO GET THESE TO FILL THEMESELVES OUT BASED ON THE INFORMATION PLACED IN THE ‘SEPT’11’ SPREADSHEET IN EACH NEW ROW. IF ANOTHER VALUE IS ENTERED IN A CELL IN COLUMN ‘C’….A 2ND MODULE (TO THE RIGHT) IN ‘SHORT SALE INCENTIVES’ AND ‘SHORT SALE OFFER ANALYSIS’ WILL AUTOPOPULATE AS WELL.

I need to find a way to do conditional formatting and disabling of a cell based on a value from another cell. For instance if A1=X,Y, or Z, then B1 will get grayed out and cell entry will be disabled. This is so that when skimming over the sheet, you can tell what cells in row B you need to put info in still, and if it's grayed you'll know you don't have to put anything there (and it won't let you). Didn't know if there was a way to do this. Any help is great, thanks!