PDA

View Full Version : [SOLVED] I need a Conditional Formatting formula



akinkaraman
03-26-2015, 10:13 AM
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
03-26-2015, 12:07 PM
is this what you were looking for?? 13077

Yongle
03-27-2015, 06:38 AM
or is it this?
13082

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
03-27-2015, 07:20 AM
his is prettier
id go with his

akinkaraman
03-27-2015, 09:19 AM
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
03-27-2015, 09:38 AM
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
03-27-2015, 09:41 AM
Indexes are changing due to the weights. Indexes are found due to the weights.

MINCUS1308
03-27-2015, 09:43 AM
13083 << is this right?

Yongle
03-27-2015, 10:30 AM
@MINCUS1308 (http://www.vbaexpress.com/forum/member.php?53956-MINCUS1308) - it looks like being prettier isn't always better!

akinkaraman
03-27-2015, 11:14 AM
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
03-28-2015, 09:02 AM
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 (http://www.vbaexpress.com/forum/member.php?53956-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
03-28-2015, 12:31 PM
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
03-28-2015, 01:01 PM
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)