I need a Conditional Formatting formula

[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?

Thanks..


Note :

I want one color one rule.

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

[MINCUS1308]

is this what you were looking for?? deneme.xlsm

[Yongle]

or is it this?
Condit_Format.jpg

Additional table created showing upper and lower limits next to your index table, adding a column for colour.
Vlookup in row24 looks up "lower limit" value of row20 in additional table (without an exact match)
Conditional formatting in row21 is dependant on value in row24
Spreadsheet attached

[MINCUS1308]

his is prettier
id go with his

[akinkaraman]

No I don't want like Yongle's file. The index values will be colored, not the periods, however MINCUS your file does not color the found index value. It colors all the same values.

At Zone E only the value which is at 1501-2000 should be colored
At Zone F the value which is at 2001-2500 should be colored
At Zone G only the value which is at 2001-2500 should be colored

because weight for Zone E is 2000 and weight for Zone F is 2250 and weight for Zone G is 2500


Thanks for your helps, could you please help me to find the right CF formula?

[MINCUS1308]

Will the weight in a zone ever change?
or will:
Zone E always be 1501-2000
Zone F always be 2001-2500
Zone G always be 2001-2500
and so on ?

[akinkaraman]

Indexes are changing due to the weights. Indexes are found due to the weights.

[MINCUS1308]

deneme (1).xlsm << is this right?

[Yongle]

@MINCUS1308 - it looks like being prettier isn't always better!

[akinkaraman]

I changed the weight of A zone to 5000 and I changed to B zone to 3000 and now no indexes are colored at A zone and B zone.

As I told before weight will be changed and new Indexes will come due to the Index chart. Only one cell has a formula that will never work.

[Yongle]

See attached workbook
It is possible to create a lot of extra problems by putting gaps between columns, merging cells etc, and , to show how much easier life can be, I tidied up your table, removing the gaps between cells, also added 2 numerical ranges for the lower and upper limits in the index. (Luckily when I moved everything, all the formulas continued to work - so was very quick)
The calculated table now sits directly above the other table and all the columns line up with each other, making all the referencing etc much easier.
(your original table of values still works too , because your index column B has not been deleted).

Compare old lookup formula in F21
=IF(IF(F20=0,"",LOOKUP(F$20,--LEFT($B$39:$B$57,SEARCH("-",$B$39:$B$57)-1),OFFSET($E$39:$E$57,,MATCH(F$22,$F$38:$Q$38,0))))=0,"N/A",IF(F20=0,"",LOOKUP(F$20,--LEFT($B$39:$B$57,SEARCH("-",$B$39:$B$57)-1),OFFSET($E$39:$E$57,,MATCH(F$22,$F$38:$Q$38,0)))))

with new much shorter formula in F26
=INDEX(F$39:F$57,MATCH(F$25-1,$E$38:$E$57,1))
To ensure index and match works for weight value = 1, there is a (hidden) zero in cell E38

=VLOOKUP(F$25,$C$39:$Q$57,4) will also work


The formula entered to control the formatting is a bit different to MINCUS1308
=ROW(A39)-38=MATCH(F$25-1,$E$38:$E$57,1)
The Match element from the formula in F26 provides the row number in the table. The -38 adjusts for difference between Excel row number and table row number

Yon

[akinkaraman]

Hello Yongle, everything is working great but I have a problem that I can not unmerge the period cells in the original file. Can't we do it without unmerging them?

[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)