# Thread: Automatic data entry based on conditional formatti (Excel XP)

1. ## Automatic data entry based on conditional formatti (Excel XP)

I have a customer that is creating what they call dashboards. We have set up 3 conditional formattings on a cell. In a column to the left we want the word for the pattern that is displayed as a result of the conditional formatting. So if the pattern is yellow for the conditionally formatted cell we want the word "yellow" to appear in the column to the left. I know about the IF statement but that gives you only two choices. I need up to four colors. Does any one have any ideas?

Thanks Fay

2. ## Re: Automatic data entry based on conditional formatti (Excel XP)

You can nest IF up to 7 levels. For example:

=IF(B4>100,"Red",IF(B4>50,"Orange",IF(B4>0,"Yellow ","Green")))

Does that help?

3. ## Re: Automatic data entry based on conditional formatti (Excel XP)

Indeed, for this problem, there's no need to nest the IFs, so you can get the full 1024 chars if needed:
=IF(B4>100,"Red","")&IF(B4>50,"Orange","")&IF(B4>0 ,"Yellow",")&IF(B4>-50,"Green","") ....
Only the very last in the chain would need a 'default' colour to be set by the ELSE part of the IF statement.
Cheers

4. ## Re: Automatic data entry based on conditional formatti (Excel XP)

If I put your formula in a cell (and add the missing double quote in the yellow test) and set B4 to 150 then I get: RedOrangeYellowGreen

I don't think that is what was wanted. Am I missing something?

5. ## Re: Automatic data entry based on conditional formatti (Excel XP)

I tried Hans's suggestion and we got Y when R should have been displayed. I then used macropod's formula as given here =IF(D15>99.99%,"G","")&IF(D15<100%,"Y","")&IF(D15< 85%,"R","") it worked like a champ except as noted by Legare. I got YR when the cell was colored red. I can almost live with that, but I bet there is a correction that is possible.

As always I appreciate your help. I always learn alot in this lounge.

Fay

6. ## Re: Automatic data entry based on conditional formatti (Excel XP)

Does

=IF(D15>=1,"G",IF(D15<0.85,"R","Y"))

help at all ?

Andrew C

7. ## Re: Automatic data entry based on conditional formatti (Excel XP)

You are two for two. Thank you so ever much.

Have a great Sunday.

Fay

8. ## Re: Automatic data entry based on conditional formatti (Excel XP)

Macropod's method of having more than 7 IF statements, works ONLY if the conditions in each of the concatenations is mutually exclusive. His example was NOT, since ALL the conditions were met if B4 were >100 (thenB4 is ALSO >50, it is > 0 and it is >-50). You want to use something more like this (to modify your example):

=IF(D15>99.99%,"G","")&IF(AND(D15<100%,D15>=85%)," Y","")&IF(D15<85%,"R","")

Now they are mutually exclusive. Though this can also be don with the combined IFs as previously posted

Another technique that I prefer, if you get into that construction and NEED to have more than 7 IFs, is to create a table. Put the limits in one column and the result in the other. Then use something like VLOOKUP to lookup the value in the table and get the result.

It works like the construction with more than 7 IFs, but it is much easier to modify. Only 1 table to change items in, no need to play with the formulas at all.

Steve

9. ## Re: Automatic data entry based on conditional formatti (Excel XP)

I just copied Hans' formula and pasted it into a worksheet and it seems to work like it should for me. Can you be a bit more specific about exactly what formula you used and what values in B4 gave incorrect results?

10. ## Re: Automatic data entry based on conditional formatti (Excel XP)

Oops!

Should have been something more like like:
=IF(AND(B4>100,B4<=50),"Red","")&IF(AND(B4>50,B4<= 0),"Orange","")&IF(AND(B4>0,B4<=-50),"Yellow","")&IF(B4>-50,"Green","Blue")