I need some help on getting this correct. I have a tracker/scheduler that shows a persons R&R dates in a line. Depending on the R&R the cells for those dates will show 1,2 or 3 (using a formula) This was orginally set up to where it would only show one R&R at a time but now I need to change it to where it will show all 3 R&Rs. The conditional formatting that I am needing to do will highlight the cell based on wheater or not the R&R has been approved. I already have a true/false table created. The problem that I am running into is getting the conditional format to determine what to look at in the table based in the cell value. This is what I have and it did not reject the formula but it is not doing anything. IF((E4)="",0,IF((E4)=1,(VLOOKUP($B$4,$B$100:$E$120,4,FALSE)="0"),IF((E4)=2,(VLOOKUP($B$4,$B$100:$E$120,6,FALSE)="0"),IF((E4)=3,(VLOOKUP($B$4,$B$100:$E$120,8,FALSE)="0")))))

When this was setup to only show the 1 R&R date the formula that was I used was this and it worked beautifullyIF((E4)="",0,(VLOOKUP($B$4,$B$100:$E$120,4,FALSE)="0")

So if anyone can help me out on this I will greatly appriciate it and thank you in advance.

Not sure how your sheet is set up, but your =VLOOKUP() won't work. The syntax of =VLOOKUP() is:

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

Your

Column to Pick Fromis going to error out.Your

Range to Look Instarts in Column B and ends at Column EYour first =VLOOKUP() counts 4 columns starting at B, so it ends at column E

but all your other's count 6 and 8 columns which is outside the range you specified.You can try changing the ranges to 4.

Something like:=IF(E4="",0,IF(E4=1,VLOOKUP($B$4,$B$100:$E$120,4,FALSE),IF(E4=2,VLOOKUP($B$4,$B$100:$E$120,4,FALSE),IF(E4=3,VLOOKUP($B$4,$B$100:$E$120,4,FALSE)))))

MIKE

Based on this statement:

Depending on the R&R the cells for those dates will show 1,2 or 3It seems like Mike's formula could be reduced to:

=IF(E4="",0,IF(E4<4,VLOOKUP($B$4,$B$100:$E$120,4,FALSE)))

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ya know, maybe I'm missing something in what you are trying to do, but a Conditional Formatting formula is supposed to return either TRUE or FALSE. The formulas in this thread all return values from a

lookup_array.You said:

I already have a true/false table createdWhy?

Why aren't you using a CF Formula that returns TRUE or FALSE directly within the CF Wizard?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I believe that's my error, he was forcing a true/false with his original formula: IF((E4)="",0,(VLOOKUP($B$4,$B$100:$E$120,4,FALSE)="0")

MIKE

Try IIF( do this, else do this)

IIF is called an Inline IF. The comma separates the true from the false.

Access2000wiz: I assume by your user name that you are familiar with Access.

While IIF may be a valid Access function, it is not valid in Excel.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

You are correct I did make that mistake my range was to short I originally have 3 tables and I did not make the adjustment to range. I corrected the range but it still is not working correctly. When I had this set up with just 1 table and only showing 1 R&R at a time the formula worked great but now I have to show 3 R&Rs at a time but just cannot get the formula to work correctly. The Found it got it corrected now. Below is the correct formula. IF((E4)="",0,IF((E4)="1",(VLOOKUP($B$4,$B$100:$K$120,4,FALSE)="1"),IF((E4)="2",(VLOOKUP($B$4,$B$100:$K$120,7,FALSE)="1"),IF((E4)="3",(VLOOKUP($B$4,$B$100:$K$120,10,FALSE)="1")))))

Thank you everyone who replyed back with out your help and extra set of eyes I probably would not have figured out the multiple mistakes that I had in the formula I posted. Thank you all and have a Merry Christams and a Happy New Years

2 items: 1 - You don't need the parenthesis around the E4's.

2 - Why do you have double quotes around all of your numbers? Double quotes tells Excel that the values are text values, not numbers.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History