Transferring one sheet information to another

Question

I have an dropped down list with the names of different sheets. I would like to make it such that when I select the appropriate name in sheet, the contents of another sheet is shown on sheet1. Is anyone familiar with such a code or if it is possible? It is actually many hidden sheets of recipes where the user can view the different recipes all in one sheet whichever the user selects. The format of the recipes can be assumed to be the same.

Any help is much appreciated.

Thank you.

Answer 1

To make it work, you'll need to use INDIRECT and ADDRESS functions.

If we have the sheet name stored in Sheet1.A2 cell, then the code would be like this for the cell B2:

=INDIRECT($A$2&"!"&ADDRESS(ROW(B2);COLUMN(B2)))

This could should be copied across the range you want to take from other sheets.

Rgds