Combining INDIRECT

[Ken]

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

 

[Fred Smith]

You can combine these conditions with AND, as in:

=AND(Indirect(...),Indirect(...),Indirect(...),Indirect(...))

Does this help?

Regards,
Fred.

 

[Ken]

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

 

[Max]

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

 

[Bob Phillips]

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

 

[Ken]

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

 

[Max]

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

 

[Ken]

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

 

[Max]

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]

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

 

[Bob Phillips]

Sorry, I missed the hidden CF bit.

 

[Max]

Welcome, Ken
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik