defined names in conditional-formatting don't work

[michelratipho]

I'm trying to use defined names in conditional formatting, whitout any
luck.

Why doesn't it work if column A is named 'ColA' and column B is named
'ColB'

I've entered a "formula is" under conditional formatting while colomn
B was selected:
= ColB > ColA

A B
8 8
1 2 (here B should turn red)
5 4


Any help is welcome. Thanks.

 

[Bob Phillips]

If you must use full column efined names to test, try

=INDEX(ColB,ROW())>INDEX(ColA,ROW())

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Brizers]

Hi M,

This is not working as Excel is evaluating the entire column and not
just the current cell (you named the columns) - do you need to name
the columns? If so, don't use these names in the conditional
formatting (just keep it to =B1>A1 & copy the formatting down).

Just to show that Excel is evaluating the whole column, try entering
1's all the way down column A & 2's all the way down column B - input
your formula (=ColB>ColA) into column B and watch the whole column
turn red!

Boo

 

[michelratipho]

Hi M,

This is not working as Excel is evaluating the entire column and not
just the current cell (you named the columns) - do you need to name
the columns? If so, don't use these names in the conditional
formatting (just keep it to =B1>A1 & copy the formatting down).

Just to show that Excel is evaluating the whole column, try entering
1's all the way down column A & 2's all the way down column B - input
your formula (=ColB>ColA) into column B and watch the whole column
turn red!

Boo

Thanks, but I was trying to make a more readable formule into
conditional-formatting, changing B1 to 'Amount' ....
I guess there's nothing like 'ColA'1 which could be copied down?

 

[michelratipho]

If you must use full column efined names to test, try

=INDEX(ColB,ROW())>INDEX(ColA,ROW())

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Tekst uit oorspronkelijk bericht weergeven -

=INDEX(ColB,ROW())>INDEX(ColA,ROW())
This is not working in conditional-formating ? Maybe I do not
understand what to do with it ;-)

 

[michelratipho]

=INDEX(ColB,ROW())>INDEX(ColA,ROW())
This is not working in conditional-formating ? Maybe I do not
understand what to do with it ;-)- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry, it is working, forgot to name the columns, THANKS!