Excel conditional formatting help?

Question

So I've got two columns in excel. In column A, I have dollar amounts, and in column B, I have text, such as gas, grocery, etc. I want to shade column A a certain color depending on the corresponding value in column B (yellow for gas, red for grocery, etc). Any idea how to do this? I set the conditional formatting based on the formulas "IF(B1="gas", A1, A1)" and "IF(B1="grocery",A1,A1)" and set the shading to a different color. It worked for that cell, but when I copy it to another cell, it doesnt update the cell numbers. ie, when I use it on A2, it still evaluates it based on B1.

When I select multiple cells (like an entire column) and enter the formula(s) for the different formats, the numbers dont update themselves :-\ for instance, here are the two columns:
A B
$4.00 gas
$5.00 grocery

I highlight column A, go to conditional formatting, for the first formula, I enter "=B1="gas"". in the second formula box, I enter "=B1="grocery"". I would hope that for cell A1, it would evaluate the formulas based on B1, and for cell A2, it would evaluate the formulas based on B2...but it doesnt :( and its driving me crazy, because it SHOULD be simple.

Nevermind, figured it out. Turns out the $ that excel automatically inserted for me keep that part of the cell "address" the same, so $A$1 kept both the same, but $A1 would just keep the letter the same, so =$K$2933="woodys" didnt work, but =$K2933="woodys" did. Yay! Now I need to find a couple other things, like how to do that, but if the corresponding cell in col B _contains_ a certain string (like lets say B contains entries like "shell gas"), and conditionally add numbers in a column based on another column (so I can easily add my gas expenses for a month, etc), but those should be A LOT easier...well, the first one, at least.

for anyone wondering, here's what I ended up doing.

for highlighting any cell in column A that corresponds to a gas charge in column B (even if the cell contains other words, such as "rent/gas" for two charges):

=ISNUMBER(FIND("gas",$B1))

That scans the entire cell for an occurance of "gas".

To add up all gas purchaces for a given period of time, I used:

=SUMIF(B2:B29,"*gas*",A2:A29)

By using the wildcard characters, I ensured that I'd get the right values.

Answer 1

Pesky Dollar signs.

To get subtotal sums use =sumif()

In your example you will use the optional Sum_range because you are not adding the text labels.

In a cell for the Grocery subtotal enter =sumif(B1:B200, "Grocery", A1:A200)

That evaluates Col B for your criteria and sums col A.

Answer 2

Select cell B1 and set the conditional format to 'Formula equals':

IF(A1="gas",TRUE,FALSE) and IF(A1="grocery",TRUE,FALSE) with the shading as you want (which is basically what you did). Then come out of conditional formatting and with cell B1 still selected, click on the format painter button (paintbrush icon on the toolbar). This will copy all the formatting in B1. Then higlight the remaining cells in column B where you want the formula to be copied to, this automatically pastes the conditional format to the other cells in column B and will update the cell references that you want.

Answer 3

First, select all the cells you want to format. Then apply that format to all the cells at once using the same formula you have. The rest of the cells will update automatically from that. But you can't copy it once you've made it. You have to do it with a selected range.

Answer 4

I think that you are going to have to create lists for col b. and then do conditional formatting based on your list.

Answer 5

I just love the way you evolved with the question :)
Just like Dorothy, you always had it in you!