Syntax for a range in an INDIRECT formula.

Microsoft Exchange server 2007 x64 dvd -...
January 12, 2011 at 12:52:22
Specs: Windows XP
Or utilize a nested if(ISNUMBER(SEARCH...(INDIRECT

[explanation - INDIRECT("'"&TEXT(TODAY(),"mm-dd")) represents a worksheet named 01-12]

Both formulas below work on their own… can’t seem to USE a range ($Q$52:$R$52), nor can I nest the two together .

=IF(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$Q$52")))=FALSE,"NO","YES")

=IF(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$R$52")))=FALSE,"NO","YES")


These don't work...
=IF(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY()+3,"mm-dd")&"'!$Q$52:$R$52")))=FALSE,"NO","YES")

=IF(OR(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$q$52")))=TRUE),
(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$r$52")))=FALSE),"YES","NO")

=IF(OR(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$q$52")))),
(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$r$52")))),"YES","NO")

This does but I can't formulate the first two formulas mentioned above into it...

=IF(OR(ISNUMBER(SEARCH("m",$A16))=TRUE),(ISNUMBER(SEARCH("x",$F16))=TRUE))),"ALS","BLS")


See More: Syntax for a range in an INDIRECT formula.

Report •

#1
January 12, 2011 at 16:37:35
I'm not quite sure what result you are looking for, since you didn't provide any examples of the data in Q52 and R52 and the expected results based on that data.

Telling us that the formulas "work" and/or "don't work" doesn't really help since we don't know what "work" means without something to base the results on.

The best I can do is offer this:

With an X in both R52 and Q52, this formula will return YES. With an X in only R52 or only Q52 or neither, it will return NO:

=IF(OR
(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$R$52")))=FALSE, ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$Q$52")))=FALSE),
"NO","YES")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 15, 2011 at 11:54:15
re: "Since the formula is searching for an "X", I didn't think I would have to "spell" it out any further"

Congratulations! That comment just got your post deleted. If you would like to try it again, go ahead, but drop the attitude.

You came here looking for help so it would probably make sense if you sounded just a tad more grateful.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 15, 2011 at 13:19:01
Attitude was not intended, frustration perhaps was conveyed too much... my apologies. I am simply trying to combine the two formulas that "work" as one formula, using them as a range instead... I simply cannot get the syntax to work.

Report •

Related Solutions

#4
January 15, 2011 at 14:00:37
Try reading Response # 1 again.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 15, 2011 at 14:14:17
This doesn't work... it needs to respond "no" if there is an "x" or "yes" if either cell has an "x"... I had something just like this but there is an "x" in Q52 so the result should be "yes". There is nothing in R52 so the answer would be "no" for that one. For some reason, excel 2003 doesn't like this. Is there not a way to use a range "Q52:R52" to check both at once, because excel doesn't like my formula at all. When I use the range, it seems to only check one cell because I can remove either "q52" or "r52" and the formula finds the "X" if there is one, but not together.

Report •

#6
January 15, 2011 at 14:42:09
re: "it needs to respond "no" if there is an "x" or "yes" if either cell has an "x""

That doesn't make sense. If either cell has an "x" then there is an "x".

You need to slow down and read what you write before you post it. I think your frustration is getting the better of you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 15, 2011 at 16:26:46
yes. I found the problem... I needed to make the search = true, not false. In that way, if it found an "x" in either one, then the result was "yes". I still want to know if there is a way to combine the two formulas into one, using a range.

e.g.

=IF(ISNUMBER(SEARCH("X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$Q$52:$r$52"))=TRUE),"YES","NO")

Why won't the above work?


Report •

#8
January 15, 2011 at 17:36:32

I'll say it one more time and then I'm done.

Give us examples of your data and the desired outputs.

You don't need ISNUMBER or SEARCH to return a YES or NO if all you are looking for is an X in one or the other cell.

Obviously, since you have been using ISNUMBER and SEARCH in all of your examples, there is something about your data that you haven't told us.

This simple formula will return Yes or No depending on the content of Q52 and R52:

=IF(OR(Q52="X",R52="X"),"Yes","No")

Since you need to combine this with INDIRECT, it would look like this:

=IF(OR(INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$Q$52")="X",INDIRECT("'"&TEXT(TODAY(),"mm-dd")&"'!$R$52")="X"),"Yes","NO")

I'm assuming you don't just have X's is Q52 and/or R52 or you wouldn't trying to use ISNUMBER and SEARCH, but I can't be sure because you haven't told us what your data looks like.

Watch how this is done. This is what I expect to see in your next post, with my X's replaced with examples of your data and the desired output.

Read the How To referenced in my signature and post a table similar to mine if you expect any more help.

I'm pretty sure I have the answer, but I'm going to waste time posting it since I don't know if it will work with your data because you haven't told us what your data looks like.

           Q        R
52         X                Output should be Yes
------------------------------------------------
           Q        R
52                  X       Output should be Yes
------------------------------------------------
           Q        R
52         X        X       Output should be Yes
------------------------------------------------
           Q        R
52                          Output should be No
------------------------------------------------

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
January 15, 2011 at 17:58:41
What you gave me is correct...

Q R
52 X Output should be Yes
------------------------------------------------
Q R
52 X Output should be Yes
------------------------------------------------
Q R
52 X X Output should be Yes
------------------------------------------------
Q R
52 Output should be No
------------------------------------------------


But I am NOT using "NO" or "YES" in my formulas, I am inserting someone's name... To explain what I'm doing, "Q52" & "R52" both represent reasons that a person, say "Rucker" is not there and if that reason is checked with an "X", then on my sheet with the formula, a "?" is placed there instead of "Rucker". Whoever is using the spreadsheet will have to insert the name of the person replacing him for that day. All I need is for "NO" or "YES" to WORK in the formula... I'll change the text later. But like I stated before, the IF(OR function worked... I just need to figure out why I can't use the formula I listed before to recognize a range. When I try to work it out by using the "fx" formula helper, it doesn't like the INDIRECT formula... it calls it VOLATILE. I know that this just boils down to a syntax or a placement thing, and that's all I am asking help with... I need the formula to be able to identify the range and see if there is an "X" in those cells.

Q R
52 X Output will be ?
------------------------------------------------
Q R
52 X Output will be ?
------------------------------------------------
Q R
52 X X Output will be ?
------------------------------------------------
Q R
52 Output will be Rucker
------------------------------------------------


Report •

#10
January 15, 2011 at 18:03:40
I don't know why the format is all messed up on the Q52 & R52. It looked fine when I typed it. Anyway, what you had is correct.

When Q52 = X output will be ?
When R52 = X output will be ?
Actually Q52 & R52 will never both = X
When neither Q52 or R52 = X output will be Rucker


Report •

Ask Question