Simple Lookup but not sure how to do it

[Flamikey]

I would like to do a simple formula where I grab a value from another
sheet which would typically look like this: =sheet1!A1, however I want
to do a lookup to a list to get the sheetname. When I try to insert a
lookup, excel returns the value I ask for but will not treat the
returned value as the sheetname, hence the second part of my formula is
not executes (going to cell A1 of the desired sheet and returning the
value for cell A1).

I am sure this is very simple for you guys, please don't laugh!!!
Thanks!

 

[Don Guillett]

As usual, you should copy/paste your efforts here for comments.

 

[Flamikey]

On the tab named "datasheet", cell A5=10

On the activesheet cell A1="Datasheet"

This formula returned the correct value of 10 and copies nicely for
adjacent cells
=INDIRECT(A$1&"!"&CELL("address",A5))

The practical purpose for this formula is that I have a seperate tab
containing the budget for each plant in a region. I wanted one tab
that pull in the YTD $$ Column for each plant tab to show a YTD summary
by plant for the region. Using the formula above I can copy one
column, paste in the next column, and all l have to do is change the
sheetname in row 1 of the new column to pull in the data from the
desired tab.

Find and Replace on the sheetname would work but I trying to dummyproof
this for the end users

 

[Don Guillett]

If copied down, this will get whatever is in cell c5 of the sheet name in
colA
=INDIRECT(a1&"!a5")