Indirect Reference?

[tinfanide]

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:

' the formula below is put on Sheet 2 Cell B1:
='Fruits'!A1

But I would like the part "'Fruits'" to be dynamic. I have tried to use INDIRECT.

' the formula below is put on Sheet 2 Cell B1:
=INDIRECT("'"&$A1&"'!A1")

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.