INDIRECT formula

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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
 
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
 
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 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
Click to expand...

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
 
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
Click to expand...

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
 
Thanks, Don, I finally understood what you were telling me....it does
work! Thank you and Pete for all the help!
Ken
 
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.
 
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?

Morallis said:
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.



Don Guillett said:
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)
Click to expand...
Click to expand...
 
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


Morallis said:
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.



Don Guillett said:
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)
Click to expand...
Click to expand...
 
Sandy Mann said:
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
Click to expand...

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


Sandy Mann said:
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


Morallis said:
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.



Don Guillett said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top