Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining INDIRECT
Thanks, Max, helps to understand a little bit sometimes....thanks for
all your help, and thanks to all! Ken |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Indirect | Excel Worksheet Functions | |||
Help with INDIRECT | Excel Worksheet Functions | |||
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
Indirect | Excel Worksheet Functions |