I need a formula for Conditional Formatting

[akinkaraman]

The index are coming due to the table below in the file. I want the generated index's in a different color at the below table with Conditional Format.

Can you help me for the formula of the CF?

I need one color with one rule.


Note :

for example

for Zone A : Weight is 1000 kg and below chart shows the index as -5 between 501-1000 section
..
for Zone F : Weight is 2250 kg and below chart shows the index as 1 between 2001-2500 section

etc..

Thanks in Advance..

 

[NARAYANK991]

Hi ,

Are you saying that the cells H21 through AD21 should be coloured ?

If these cells are to be coloured according to their index values , there are dozens of values ; how can so many rules and associated colours be used ?

Narayan

 

[akinkaraman]

No. F39-Q57 will be collored due to Index values which are found due to weight at the up chart.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[akinkaraman]

As you see it colors more than one values at Zone E, Zone F and Zone G

Zone E 2000 kgs Index -1 and it should color -1 which is at 1501-2000 section
Zone F 2250 kgs Index 1 and it should color 1 which is at 2001-2500 section
Zone G 2500 kgs Index 3 and it should color 3 which is at 2001-2500 section

It also colors same values at different sections too now.

 

[NARAYANK991]

Hi ,

The problem always is an incorrect / incomplete specification !

The emphasis was on the Zone and the Index , not on the weight.

If the Weight is also required , there was an earlier file where the weights have been split up into the Lower Value and the Upper Value ; can you not upload that file ? We just keep doing the same things over and over again.

Narayan

 

[NARAYANK991]

Hi ,

See if this is correct.

Narayan

 

[akinkaraman]

Yes Narayan it is working good now. Thank you very much.

Is there a way to remove the reference table? So we can combine them and embed the reference table into Conditional Format..

 

[NARAYANK991]

Hi ,

I have put the reference table just so that you can follow the logic.

You can delete the table ; only the two columns labelled LL and UL are necessary. If you want this also to be eliminated , it will make the formula unnecessarily lengthy.

Narayan

 

[akinkaraman]

Thanks Narayan. The original file is different and I will try to hide LL and UL columns and convert to it. Have a nice day. Thank you..

 

[akinkaraman]

I think I solved the problem with a working Conditional Formating formula..

=SUMPRODUCT(--(((($H$22:$AE$22=F$38)*$H$20:$AE$20)>=--LEFT($B39,SEARCH("-",$B39)-1))*(((($H$22:$AE$22=F$38)*$H$20:$AE$20)<=--MID($B39,SEARCH("-",$B39)+1,255)))>0)

 

[akinkaraman]

Finally

 

[akinkaraman]

Hi ,

The problem always is an incorrect / incomplete specification !

The emphasis was on the Zone and the Index , not on the weight.

If the Weight is also required , there was an earlier file where the weights have been split up into the Lower Value and the Upper Value ; can you not upload that file ? We just keep doing the same things over and over again.

Narayan

Hi Narayan, I've seen this thing you mentioned just now. Because of I didn't want to make more confuses again I cut the Lower Deck and Upper Deck and I only showed total values which we would work. Now I guess the problem is solved. I found the formula from another excel sheet and tried to convert it into my sheet in half an hour. I understand how much hard works that you are dealing with. I feel like I will never learn this much. My brain is stopped now. lol...