INDIRECT Help

[Thanks]

Hello
I am having trouble with an INDIRECT formula. My formula on sheet qtr 1:
INDIRECT("INDEX(Myrange"&RIGHT(SHEETNAME,1)&",1,"&COLUMN()+1&")")
refering to named range MyRange1. This formula returns #value!. When I
step through the formula the sheetname returns 2 but if I put the formula
sheetname() in a cell byitslef it returns 1. I do have a range named
MyRange2 but yet it doent even return a wrong answer.
Any ideas?

 

[T. Valko]

I have no idea what you're trying to do with that but for starters you don't
want quotes around any functions:

=INDIRECT("INDEX(Myrange"&RIGHT(SHEETNAME,1)&",1,"&COLUMN()+1&")")

What is "SHEETNAME"? Is that a UDF or a named formula that returns the sheet
name?

It looks to me like you might want something like this:

=INDEX(INDIRECT("Myrange"&RIGHT(SHEETNAME)),1,COLUMN()+1)

However, if "Myrange" is a dynamic range defined using functions like OFFSET
then this won't work.