Condtional formatting from last non-blank cell

[rayted]

Hi all,

i have column A2:A20 populated with customer names

From column B2-Z2 I have a number for each month. B1 = July, C1= AUgust and so

I want a conditional formatting for A2 to be populated from the last non-blank cell from the range b2:z2. Can someone advise pleasde?

thank you!

[AliGW]

Conditional formatting??? You cannot populate a cell with CF, but you can format it.

Explain what you want A2 to look like.

Last non-blank:

=LOOKUP(2,1/(B2:Z2<>""),B2:Z2)

[FDibbins]

Also note that a cell with a formula in it is not (necessarily) blank

[rayted]

Hi AliGW,

sorry yes i meant to say A2 would be highlighted deending on the value of what is in the last non-blank cell, how do I add to your formula, "1" (as in look up the last non-blank cell which is equal to 1), I will be writing different fomulas on A2 as well, for cells such as 1 (red), 2 (orange), 3 (green)

thank you

[AliGW]

Try this:

=LOOKUP(2,1/(B2:Z2=1),B2:Z2)

A word of caution: don't go overboard on the colour-coding. Your workbooks (if I remember rightly) are already a bit of an assault on the senses. Ask yourself is it REALLY necessary to have a different colour for each of the 12 months (or quarters, or whatever) - the more colours a user has to evaluate, the harder it is, and a significant proportion of men are colour blind (around 1 in 12: http://www.colourblindawareness.org/colour-blindness/).

LESS IS MORE!!!

[rayted]

Hi AliGW

It is working intermittendly.

Can you take a look at A2 attached please and advise

Indeed less is more :-) noted your feedback thsnk you

[AliGW]

You will need this:

=LOOKUP(2,1/(B2:Z2<>""),B2:Z2)=1

=LOOKUP(2,1/(B2:Z2<>""),B2:Z2)=2

=LOOKUP(2,1/(B2:Z2<>""),B2:Z2)=3

etc.

[davsth]

You need to understand the formula in the cf
=LOOKUP(2,1/(B2:Z2=1),B2:Z2) returns 1 as it finds the last column with a 1 in and then its value 1!
=LOOKUP(2,1/(B2:Z2>0),B2:Z2) returns the last value >0. This should probably be your base

cf =LOOKUP(2,1/(B2:Z2>0),B2:Z2) =1 red etc

[AliGW]

Example of post #7 attached.

[AliGW]

Thanks for the rep.

[rayted]

thank you AliGW!

[rayted]

sorry new question, is there a way to use the formula

=LOOKUP(2,1/(B2:Z2<>""),B2:Z2)=1

but change b2:z2 to c2, e2, h2, j2 (so it looks at c2, e2, h2, j2)?

thank you

[AliGW]

You can't, but you can do this:

=LOOKUP(2,1/($B$2:$Z$3="Overall Score"),$B4:$Z4)=1

Or you might need this:

=LOOKUP(2,1/(($B$2:$Z$3="Overall Score")*($B4:$Z4<>"")),$B4:$Z4)=1

[rayted]

thank you AliGW

should do the trick (2nmd formula!)

[AliGW]

Good, good!

[rayted]

Hi AliGW.all

Upon adding in new company info my conditional formatting is not working and the ranges seem out of sync.

I.e. if I change data in Cell C5, it affectsd A6 instead of A5..... and A4 just doesn't change colour despite me chanhging the overall score in C4?

E4 scores does not have an impact either (the CF setup on cells in column A are using the last non blank data...........) please help!

[AliGW]

If you add rows at the top, it will mess up the formatting. Add rows only between the first and the last.

If you need to sort tables, then use structured tables and always add rows at the bottom and then sort.

[rayted]

thanks AliGW!

[AliGW]

You're welcome.