Combining INDIRECT
 

Hi Group,
I have the following formula used in conditional formatting:

=INDIRECT("I"&ROW())="AB"
=INDIRECT("I"&ROW())="A"
=INDIRECT("I"&ROW())="AC"
=INDIRECT("I"&ROW())="T"

I have 4 different conditions so I have 4 different CF rules. Can they
be combined into one? Thanks in advance for any help!
Ken

Combining INDIRECT
 

You can combine these conditions with AND, as in:

=AND(Indirect(...),Indirect(...),Indirect(...),Ind irect(...))

Does this help?

Regards,
Fred.

"Ken" wrote in message
...
Hi Group,
I have the following formula used in conditional formatting:

=INDIRECT("I"&ROW())="AB"
=INDIRECT("I"&ROW())="A"
=INDIRECT("I"&ROW())="AC"
=INDIRECT("I"&ROW())="T"

I have 4 different conditions so I have 4 different CF rules. Can they
be combined into one? Thanks in advance for any help!
Ken

Combining INDIRECT
 

Hi Fred,
I tried as you suggested and came up with the following:

=AND(INDIRECT("I"&ROW())="A"),INDIRECT("I"&ROW())= "T"),INDIRECT("I"&ROW
())="AC"),INDIRECT("I"&ROW())="AB"))

It gives me an error message that something is wrong with the
formula....have I missed something?
Thanks for your help!
Ken

Combining INDIRECT
 

=AND(INDIRECT("I"&ROW())="A"),INDIRECT("I"&ROW())= "T"),INDIRECT("I"&ROW())="AC"),INDIRECT("I"&ROW()) ="AB"))

Believe it should be an =OR(indirect(1),indirect(2),...) in this instance?
All of the indirects point to the same cell, which can of course only house
a certain value at any one time
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Combining INDIRECT
 

=OR(INDIRECT("I"&ROW())={"A","T","AC","AB"})

--
__________________________________
HTH

Bob

"Ken" wrote in message
...

Hi Fred,
I tried as you suggested and came up with the following:

=AND(INDIRECT("I"&ROW())="A"),INDIRECT("I"&ROW())= "T"),INDIRECT("I"&ROW
())="AC"),INDIRECT("I"&ROW())="AB"))

It gives me an error message that something is wrong with the
formula....have I missed something?
Thanks for your help!
Ken

Combining INDIRECT
 

Thanks to all....
I still get the "formula contains an error" with OR instead of AND,
and Bob's formula gives me "cannot use unions, intersects, or array
constants..." etc. I'm using Excel 2007, if that's a problem. It looks
like either of the formulas should work, but still getting
errors....Thanks for any and all help!
Ken

Combining INDIRECT
 

.. get the "formula contains an error" with OR ..

Try it like this, using your xl07 equivalent
for the CF's Formula Is (in xl2003):
=OR(T(INDIRECT("I"&ROW()))="A",T(INDIRECT("I"&ROW( )))="T")

Above tested here, works ok
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Combining INDIRECT
 

Thanks, Max, that does indeed work! Thank you so much....I can start
eliminating all the extraneous rules....just one question: what is the
T function?? Just wanting to learn!
Ken

Combining INDIRECT
 

Actually, think you could make the CF work using the simpler:
=OR($I1="A",$I1="T")
(just ensure that the active cell is on row1 in the CF range selection)

As for T, Excel's Help says:
If value is or refers to text, T(value) returns the value

Assume you have an "A" in I2

Without the T(...)
INDIRECT("I"&ROW()) will return it as an array: {"A"},
which isn't quite the same as "A"

With the T(...)
T(INDIRECT("I"&ROW())) =T({"A"}) ="A",
which returns the correct comparison as TRUE
ie T(INDIRECT("I"&ROW()))="A" is TRUE

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Ken" wrote in message
...
Thanks, Max, that does indeed work! Thank you so much....I can start
eliminating all the extraneous rules....just one question: what is the
T function?? Just wanting to learn!
Ken

Combining INDIRECT
 

Thanks, Max, helps to understand a little bit sometimes....thanks for
all your help, and thanks to all!
Ken

Combining INDIRECT
 

Sorry, I missed the hidden CF bit.

--
__________________________________
HTH

Bob

"Ken" wrote in message
...
Thanks to all....
I still get the "formula contains an error" with OR instead of AND,
and Bob's formula gives me "cannot use unions, intersects, or array
constants..." etc. I'm using Excel 2007, if that's a problem. It looks
like either of the formulas should work, but still getting
errors....Thanks for any and all help!
Ken

Combining INDIRECT
 

Welcome, Ken
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Ken" wrote in message
...
Thanks, Max, helps to understand a little bit sometimes....thanks for
all your help, and thanks to all!
Ken