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:
Code:
' 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.
Code:
' 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.