Condtional Format based on Lookup Value?

[DGATES8]

So i have a table of data see attached file for example of data:

Excel Forum Example.xlsx

The sheet is supposed to compare the data in the table to its relevant SOM bracket and then highlight with CF if the value exceeds the screening value.
What happens here is the SOM bracket on row 5 is calculated based on the figure in row 14.
After that what i want to do is condtionally format the data in range h7:k13. So to follow the data through for a specific example this is what i want to happen.

E.g. K7

The value is 14.1 and (following the same sample down) the SOM is 3.4. The correct SOM bracket for this data is 6% SOM. (i have already figured out the formula for this).
Going to the range c6:e6 we can then find the correct SOM and corresponding screening values. This should then imply the corresponding determinant for cell k7 is in cell e7.

I want excel to then decide 'is k7 greater than e7'. If it is then highlight it to the user.

I cannot wrap my brain around how to do this.. I feel like it may require a h or vlookup within a conditional format equation... but i may be wrong.

Does anyone have any ideas on this?

Regards,

Dan

[AliGW]

The formula for CF for cell K7 is:

=K7>E7

[Glenn Kennedy]

I'm a bit lost..... How does the value of 3.4 in K14 influence the outcome of this???

Also, you say you have some formulae here - but htere are none in your example.

[Glenn Kennedy]

Yea - is it just as simple as Ali has suggested??? If so, I've horribly mis-read your request.

[AliGW]

I think this is the key request:

I want excel to then decide 'is k7 greater than e7'. If it is then highlight it to the user.

I don't really understand the rest ...

[JohnTopley]

This formula will retrieve the value in E7

=INDEX($C$7:$E$13,,MATCH(K$14,$C$6:$E$6,1)) using K14 as SOM value (But why not the SOM values in row 5?)

You should able to adapt it for the other columns.

For CF you could then use =K7>INDEX($C$7:$E$13,,MATCH(K$14,$C$6:$E$6,1)) and apply to K7 to K13

Hope this helps.

[DGATES8]

I have ammended the file slightly for Glenn K. See attached.

Excel Forum Example.xlsx

Glenn K: Sorry i have inserted the formula now. I have taken this data from another sheet and only pasted the values not the formulae.
The SOM in row 14 is raw data. Then I then put it into one of three brackets 1% 2.5% and 6% (calculated by row 5).

I understand basic conditional formatting so thats not the issue so much.
John Topley is on the right tracks of what im trying to do here i feel. However this formula is only returning values from determinand A only.

What it needs to do is for any given cell in the range h7:k13 [e.g. i9] it should read UP to the SOM from row 5 [2.5%] (which has been calculated) then go across to the determinand on that row [determinand c] and the corresponding SOM [2.5%] to find the screening value [510].
The value in i9 (0.74) should then be compared to see if it is greater than c9 (510) in this case, no.

Hope that helps the description.

[protonLeah]

Please Login or Register  to view this content.

Screen_Header, $C$6:$E$6
Screening_Table, $C7:$E$13
SOM_Bracket, $H$5:$K$5

[DGATES8]

protonLeah - This worked great Thanks.
Just what i needed.

Many Thanks,

Dan

[DGATES8]

I am struggling to apply this conditional formatting to the larger table of data.

See my attached excel file.

Basic Summary Sheet.xlsx

The formatting appears to be still looking at the top line and cannot be applied to the lower rows.

Can i ask what the index column figure '-7' is doing in this equation?
Or if someone could explain the whole equation that may help too.
I am struggling to understand why this wont apply when I am formatting it.

Regards,

[6StringJazzer]

Participants in this thread, please see follow-up question in

http://www.excelforum.com/excel-form...ting-help.html

Thanks.