INDIRECT formula

[Ken]

Hi again group!
A small question. I am using an INDIRECT formula for turning a row a
certain color, and it works well. How would one turn this formula into
a multiple criteria formula using the INDIRECT method:
=INDIRECT("I"&ROW())="T"
What I been trying to do is use an IF, OR, but I always get the
"error" message.
I've searched all over the internet for an example but no luck. The 3
criteria are:
=INDIRECT("I"&ROW())="T"
or
=INDIRECT("I"&ROW())="AB"
or
=INDIRECT("I"&ROW())="AC"
I can accomplish this with 3 seperate rules, but it's just a little
"clunky".
It's really no big deal, just trying to learn something new....any
advice is greatly appreciated! My "Thanks" in advance!
Ken

 

[Pete_UK]

To apply the conditional formatting in one rule, try it like this:

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

Hope this helps.

Pete

 

[Don Guillett]

Just use this and copy the format
=OR(I8="t",I8="u",I8="v")

 

[Don Guillett]

tested?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
To apply the conditional formatting in one rule, try it like this:

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

Hope this helps.

Pete

 

[Ken]

Don and Pete,
I tested Pete's suggestion, but it does not work in Excel
2007...getting ready to test Don's suggestion (does it color the whole
row?)
Ken

 

[Ken]

Don and Pete,
   I tested Pete's suggestion, but it does not work in Excel
2007...getting ready to test Don's suggestion (does it color the whole
row?)
Ken

Don and Pete,
I tested the formula, and it evaluates to "True" or "False" if the
cell value is "T", "AB", or "AC", and fills in the cell with true or
false and I need the cell's value to remain, and color the whole row
if the value in column I is a "T", "AB". or "AC". I don't guess I was
making my question very clear, and I appreciate all the help!
Ken

 

[Don Guillett]

use the formula I gave you BUT highlight the row number on the far left and
use absolutes
=OR($I$8="t",$I$8="u",$I$8="v")


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don and Pete,
I tested Pete's suggestion, but it does not work in Excel
2007...getting ready to test Don's suggestion (does it color the whole
row?)
Ken

Don and Pete,
I tested the formula, and it evaluates to "True" or "False" if the
cell value is "T", "AB", or "AC", and fills in the cell with true or
false and I need the cell's value to remain, and color the whole row
if the value in column I is a "T", "AB". or "AC". I don't guess I was
making my question very clear, and I appreciate all the help!
Ken

 

[Ken]

Thanks, Don, I finally understood what you were telling me....it does
work! Thank you and Pete for all the help!
Ken

 

[Don Guillett]

Glad to help

 

[Morallis]

Hi,

I've got the same problem I'm trying to solve. I'm not quite sure what the
outcome was here, could someone explain this a little to me please.


The criteria were originally looking at the column and then colouring the
row according to the columns value.

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

We need these merged into one single rule for the conditional formatting.

This doesn’t seem to work:
=OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="AB",INDIRECT("I"&ROW())="AC")

And the line: =OR(I8="t",I8="u",I8="v") only refers to the specific cell I8

How do you add the three criteria [=INDIRECT("I"&ROW())="T",
=INDIRECT("I"&ROW())="AB" and =INDIRECT("I"&ROW())="AC"] into one rule so
that if the value in column I =T, AB or AC the whole row is highlighted with
the fill colour specified in the conditional formatting?

Sorry, I’m quite new to this and honestly just learning slowly from forums
and other things I read. I’m actually looking for some study material to work
through.

I hope someone can help me with my little problem for now though

Thanks.

 

[Morallis]

I think i just figured out my own problem.. it was quite simple too :S

I just replaced I8 with $I8

Does this make sense?

Hi,

I've got the same problem I'm trying to solve. I'm not quite sure what the
outcome was here, could someone explain this a little to me please.


The criteria were originally looking at the column and then colouring the
row according to the columns value.

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

We need these merged into one single rule for the conditional formatting.

This doesn’t seem to work:
=OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="AB",INDIRECT("I"&ROW())="AC")

And the line: =OR(I8="t",I8="u",I8="v") only refers to the specific cell I8

How do you add the three criteria [=INDIRECT("I"&ROW())="T",
=INDIRECT("I"&ROW())="AB" and =INDIRECT("I"&ROW())="AC"] into one rule so
that if the value in column I =T, AB or AC the whole row is highlighted with
the fill colour specified in the conditional formatting?

Sorry, I’m quite new to this and honestly just learning slowly from forums
and other things I read. I’m actually looking for some study material to work
through.

I hope someone can help me with my little problem for now though

Thanks.

Just use this and copy the format
=OR(I8="t",I8="u",I8="v")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Sandy Mann]

If you try to use the formula:

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

in conditional formatting you will get the error message:

"You may not use unions, intersections, or array constants for conditional
formatting criteria"

The ROW() function is returning a one element array like: {I8} I believe
this is causing the formula to fail even although you do not get an error
message.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Hi,

I've got the same problem I'm trying to solve. I'm not quite sure what the
outcome was here, could someone explain this a little to me please.


The criteria were originally looking at the column and then colouring the
row according to the columns value.

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

We need these merged into one single rule for the conditional formatting.

This doesn't seem to work:
=OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="AB",INDIRECT("I"&ROW())="AC")

And the line: =OR(I8="t",I8="u",I8="v") only refers to the specific cell
I8

How do you add the three criteria [=INDIRECT("I"&ROW())="T",
=INDIRECT("I"&ROW())="AB" and =INDIRECT("I"&ROW())="AC"] into one rule so
that if the value in column I =T, AB or AC the whole row is highlighted
with
the fill colour specified in the conditional formatting?

Sorry, I'm quite new to this and honestly just learning slowly from forums
and other things I read. I'm actually looking for some study material to
work
through.

I hope someone can help me with my little problem for now though

Thanks.

Just use this and copy the format
=OR(I8="t",I8="u",I8="v")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Sandy Mann]

The ROW() function is returning a one element array like: {I8} I believe
this is causing the formula to fail even although you do not get an error
message

So much for much knowledge of Excel!

=INDIRECT("I"&ROW())=6

works just fine With 6 in I8 so it is not the INDIRECT() and the ROW()
together that is the problem.

Equally well

=OR(ROW()=8,ROW()=7)

also works fine in I8 so it is not the OR() or the ROW() together that is
the problem.

Finally:

=OR(INDIRECT($H$1)=6)

with I8 in H1 again works so it seems as if it is only when you use OR(),
INDIRECT() and ROW() in CF together thus:

=OR(INDIRECT("I"&ROW()))=6

that it fails

--
NOWH
(None Of Which Helps!)

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

If you try to use the formula:

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

in conditional formatting you will get the error message:

"You may not use unions, intersections, or array constants for conditional
formatting criteria"

The ROW() function is returning a one element array like: {I8} I believe
this is causing the formula to fail even although you do not get an error
message.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Hi,

I've got the same problem I'm trying to solve. I'm not quite sure what
the
outcome was here, could someone explain this a little to me please.


The criteria were originally looking at the column and then colouring the
row according to the columns value.

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

We need these merged into one single rule for the conditional formatting.

This doesn't seem to work:
=OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="AB",INDIRECT("I"&ROW())="AC")

And the line: =OR(I8="t",I8="u",I8="v") only refers to the specific cell
I8

How do you add the three criteria [=INDIRECT("I"&ROW())="T",
=INDIRECT("I"&ROW())="AB" and =INDIRECT("I"&ROW())="AC"] into one rule
so
that if the value in column I =T, AB or AC the whole row is highlighted
with
the fill colour specified in the conditional formatting?

Sorry, I'm quite new to this and honestly just learning slowly from
forums
and other things I read. I'm actually looking for some study material to
work
through.

I hope someone can help me with my little problem for now though

Thanks.

Just use this and copy the format
=OR(I8="t",I8="u",I8="v")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi again group!
A small question. I am using an INDIRECT formula for turning a row a
certain color, and it works well. How would one turn this formula into
a multiple criteria formula using the INDIRECT method:
=INDIRECT("I"&ROW())="T"
What I been trying to do is use an IF, OR, but I always get the
"error" message.
I've searched all over the internet for an example but no luck. The 3
criteria are:
=INDIRECT("I"&ROW())="T"
or
=INDIRECT("I"&ROW())="AB"
or
=INDIRECT("I"&ROW())="AC"
I can accomplish this with 3 seperate rules, but it's just a little
"clunky".
It's really no big deal, just trying to learn something new....any
advice is greatly appreciated! My "Thanks" in advance!
Ken