CODEWITHSUNDEEP
excelvba
Jeylani Peer
Jeylani Peer

Reputation: 21

Transferring one sheet information to another

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.

Upvotes: 0

Views: 465

Answers (1)

Tiago Cardoso
Tiago Cardoso

Reputation: 2097

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

Upvotes: 1

Related Questions