Searching for name of sheet?

[mark_98034]

I am trying to search for a given cell (B2) based off of the name of the sheet?

I have 10 different sheets with various names, but the value I want off of all the sheets is in B2.

When I load the download the data onto the sheets the value is erased. For example. I have a sheet named Sheet1 and want the value in B2. On my master sheet I have tried =Sheet1!B2 but each time I refresh the data on the Sheet1 I get a #REF! error.

Thanks for any help!

Mark

 

[Tony Miall]

What's happening when you download the data onto the sheets? What I mean is if you are inserting rows the reference will be below the inserted data, hence #REF.

If this is right use =INDIRECT("Sheet1!B2")

 

[konew1]

Are you just putting new data into Sheet1!B2? Or into other cells that populate it? It sounds like you are deleting cell Sheet1!B2, not just replacing its contents.
If you are deleting the cell, and for some reason want to continue to delete it, then you can use OFFSET function to get a result. Post back more detail.

 

[mark_98034]

Thanks! The Indirect formula worked.