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?

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,COLU MN()+1)

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


--
Biff
Microsoft Excel MVP


"Thanks" wrote in message
...
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?