On Sheet 1 (named "Fruits"), there is a value (e.g. "Apple") on Cell A1.



Now, on Sheet 2, I would like to display the value of Sheet 1 Cell A1 on Cell B1 of Sheet 2. And there is a value "Fruits" (the name of Sheet 1).

If I just reference it the simplest way, the formula is:
But I would like the part "'Fruits'" to be dynamic. I have tried to use INDIRECT.
However, if the red part (the cell reference of Sheet 1 target cell) is put within the INDIRECT function parentheses, that cell reference is not going to be dynamic. Is there a way to achieve it?

Any help is appreciated.

You could combine it with an OFFSET function, like:
You can replace ROW()-1 & COLUMN()-1 with whatever other functions/values you require to get the desired results.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks! OFFSET is the solution.