Referencing a Sheet Name in a Cell

Try this to get your started on your formula,

=INDIRECT(B1&"a3")

Where B1 houses Sheet1! and A3 is the cell to reference

Brian,

Thanks for the quick reply.

I adjuted your formula slightly so that it would work for other users that migh now know aboiut adding a "!" in a sheet name
=indirect(a1 & "!" & b1)
Where A1 is the sheet name and B1 is the cell you want in the sheet.

I did run into a problem with this formula.

If there is a space in the sheet name then the formula fails. I tried working around the problem with quotes etc, but could not get that to work. Do you have any ideas?


Also I was really hoping for a forumul that referended another sheet in such a way that I could copy the formula down and the Cell reference would update just like it does in a single sheet.

I would be after something like
=indirect($a$1 &"!")&c1 <-- but that does not work

That way if i copied the formula the first result would return the reulst in b1 from the sheet referenced in a1, then copying down would then givet he results in b2, b3, etc.


Not sure my last part is clear.

again thanks for your help.

Alan

Just wanted to follow up on my own post to help out anyone else trying this.

As I said before I could not get the indirect function to work with sheets that had spaces.

Well It turns out this also won't work with sheets that have a dash "-" as in "sheet-1". However an underscore "_" does seem to work.

Anyone have any suggestions on working around these limitations?

Try :

=INDIRECT("'"&$A$1&"'!"&B1)

This should sort out your space problem by enclosing the sheet name in single quotes

Following up on my most from last year ...

As INDIRECT is a volatile function I was wondering about using INDEX for the same thing?

I saw a few references to using INDEX instead of INDIRECT, but when I tried to implement them things did not go how i hoped.

When can one use INDEX instead of INDIRECT?

Here were a few simple examples that I was not able to convert
=INDIRECT("'" & B1& "'"&"!" & "A1")
=INDIRECT("'" & B1& "'"&"!" & C1)

and here is my "live" one I am trying to optimize and make more efficient
INDIRECT("'" & $W$2 & "'!" & "A:A")

inside of

=IF(B3="-","-",MID(INDIRECT("'" & $W$2 & "'!" & "A1:A50000"),9,10))

INDIRECT("'" & $W$2 & "'!" & "A1:A50000")


thanks for the help and information

Alan

If I'm not mistaken, if you download and install the free add-in Morefunc.xll, you can use INDIRECT.EXT, where you can set the second argument to FALSE so that it's not volatile.

Hope this helps!