|
INDIRECT formula
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 a =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 |
INDIRECT formula
To apply the conditional formatting in one rule, try it like this:
=OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="A B",INDIRECT("I"&ROW())="AC") Hope this helps. Pete On Jul 6, 1:30*pm, Ken wrote: 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 a =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 |
INDIRECT formula
|
INDIRECT formula
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 |
INDIRECT formula
On Jul 6, 9:17*am, Ken wrote:
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 |
INDIRECT formula
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 "Ken" wrote in message ... On Jul 6, 9:17 am, Ken wrote: 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 |
INDIRECT formula
Thanks, Don, I finally understood what you were telling me....it does
work! Thank you and Pete for all the help! Ken |
INDIRECT formula
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... Thanks, Don, I finally understood what you were telling me....it does work! Thank you and Pete for all the help! Ken |
INDIRECT formula
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 doesnt seem to work: =OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="A B",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, Im quite new to this and honestly just learning slowly from forums and other things I read. Im 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" wrote: Just use this and copy the format =OR(I8="t",I8="u",I8="v") -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... 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 a =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 |
INDIRECT formula
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" wrote: 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 doesnt seem to work: =OR(INDIRECT("I"&ROW())="T",INDIRECT("I"&ROW())="A B",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, Im quite new to this and honestly just learning slowly from forums and other things I read. Im 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" wrote: Just use this and copy the format =OR(I8="t",I8="u",I8="v") -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... 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 a =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 |
INDIRECT formula
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 Replace @mailinator.com with @tiscali.co.uk "Morallis" wrote in message ... 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())="A B",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" wrote: Just use this and copy the format =OR(I8="t",I8="u",I8="v") -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... 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 a =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 |
INDIRECT formula
"Sandy Mann" wrote in message
... 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 Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Morallis" wrote in message ... 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())="A B",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" wrote: Just use this and copy the format =OR(I8="t",I8="u",I8="v") -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... 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 a =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 |
| All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com