Conditional Formatting not working

[sarahrandall3216]

We have booths for rent and I have put together a sheet that I can list the dealer #, the booth # they occupy, the sales $, and it will calculate the sales per square foot. The booths are classed as different types based on the location of the booth (Type 1=1st floor front, Type 2=1st floor back, etc.) I have this information in a table (BoothSqFt) with the columns Booth #, Booth SqFt, Booth Type. Back to the sales per square foot sheet, when I enter the booth # I want the cell to turn a corresponding color based on the booth type (Type 1=Red, Type 2=Orange, etc.)

I have the formula =VLOOKUP(E47,BoothSqFt,3,FALSE)=3 that is returning "TRUE" or "FALSE" correctly in a regular cell. I was going to use 5 of these statements (one for each type, =1, =2, =3, etc.) Once I copy and paste the formula into Conditional Formatting, it tells me the "There is a problem with this formula" and it refuses to work. I've even added absolute values and it doesn't work. What is wrong with my formula? Ideas on making it better so I don't have to use 5 formulas?

I can give more info if needed.

 

[Ken Cowen]

If you have 5 conditions and 5 format, you will need 5 conditional formats. More than 3 conditions is supported in the newer Excel versions, not sure when it started.
Does your format depend on a value of 1 through 5 that exists on the worksheet? Or is that value something that needs to be looked up in the conditional format equation? Either will be okay, but you may want to start with basing the conditional format on a worksheet number, at least to troubleshoot. Each of the conditional format rules needs to return a value of True if you want the format to be applied. The last True format will be applied, unless you flag it to stop when a condition is met. Problems with conditional formatting usually come down to absolute and relative range issues. Does your format apply to a single cell (E47?) Or a range of cells? Is the booth type (1,2,3,4,5) in the 3rd column of the table? Does =Vlookup(e47,boothsqft,3,false) return 3? It is hard to get the "problem with formula error" message if the formula can be evaluated. Make sure it starts with = and the parentheses are all okay. If you paste it directly from the worksheet, where it evaluates correctly is should work, unless a problem with the ranges is created when applying the formula to multiple cells.
Ken

 

[sarahrandall3216]

If you have 5 conditions and 5 format, you will need 5 conditional formats. More than 3 conditions is supported in the newer Excel versions, not sure when it started.
Does your format depend on a value of 1 through 5 that exists on the worksheet? Or is that value something that needs to be looked up in the conditional format equation? Yes it needs to be looked up in the formatting. I don't want to add hidden cells if it's not necessary. The Vlookup is searching a table where these values exist. The table can change which is why I decided to go with Vlookup and and Table set-up. Is the booth type (1,2,3,4,5) in the 3rd column of the table? Yes.

Either will be okay, but you may want to start with basing the conditional format on a worksheet number, at least to troubleshoot. Each of the conditional format rules needs to return a value of True if you want the format to be applied. The last True format will be applied, unless you flag it to stop when a condition is met. I wrote the formulas in test cells in the worksheet and built it until it was correctly returning "True" or "False". I've tested it with multiple sections of the worksheet and the formula returns "True" or "False" correctly in each section with no editing. Each Vlookup will return only one number 1-5. So only one of the 5 formulas will test "True" and all others should test "False".


Problems with conditional formatting usually come down to absolute and relative range issues. Does your format apply to a single cell (E47?) Or a range of cells? It includes a range of cells. The ranges will look like this: D2:N2, D5:N5, D8:N8, etc. It's every third row starting on the second row. I was testing it with one cell before I applied it to a range, but range doesn't work either.


Does =Vlookup(e47,boothsqft,3,false) return 3? Yes. True.

It is hard to get the "problem with formula error" message if the formula can be evaluated. In "Formula Auditing" I went through the "Evaluate Formula" and every time it worked.

Make sure it starts with = and the parentheses are all okay. Done and yes.

If you paste it directly from the worksheet, where it evaluates correctly is should work, unless a problem with the ranges is created when applying the formula to multiple cells. This is must be the answer because I'm totally out of ideas!! Help!

Ken[/QUOTE]

 

[Ken Cowen]

When you paste in the formula, make sure the top left cell is selected. Make sure you have relative ranges to the cells you are looking up; absolute range to the lookup table (this should be okay since you use a range name).
Ken