Multiple IF statements in a Conditional Forma

December 16, 2010 at 23:08:20
Specs: Windows XP
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 beautifully

IF((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.


See More: Multiple IF statements in a Conditional Forma

Report •


#1
December 17, 2010 at 08:18:46
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 From is going to error out.

Your Range to Look In starts in Column B and ends at Column E

Your 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

http://www.skeptic.com/


Report •

#2
December 17, 2010 at 09:33:08
Based on this statement:

Depending on the R&R the cells for those dates will show 1,2 or 3

It 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.


Report •

#3
December 17, 2010 at 09:46:13
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 created

Why?

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.


Report •

Related Solutions

#4
December 17, 2010 at 09:48:51
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

http://www.skeptic.com/


Report •

#5
December 17, 2010 at 13:25:58
Oh..my bad.

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


Report •

#6
December 18, 2010 at 16:12:00
Try IIF( do this, else do this)
IIF is called an Inline IF. The comma separates the true from the false.

Report •

#7
December 18, 2010 at 17:10:22
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.


Report •

#8
December 20, 2010 at 23:47:44
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


Report •

#9
December 21, 2010 at 04:22:08
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.


Report •


Ask Question