Copy cell value from another sheet based on drop down list

Hi All,

Perhaps a bit of a tough one to explain but I need help with the following if possible. Thanks.

I have a dynamic drop down list on sheet 1 of my workbook that has my list of different sheets in my work book. This list can change depending on how many sheets I add. The sheets are listed by rooms in a house. IE: If I decide that I need a Bedroom, Bathroom, Kitchen etc. I add these sheets and this drop down list displays the # of sheets within the workbook and the names that I have created for each sheet.

On sheet 1, Cell A1, I have this drop down list. Then below, in Cell A2 of sheet 1, I have another cell that references a cell (B2) from a different sheet depending on what sheet from the drop down list is selected. What I am trying to do is auto populate cell A2 of sheet 1 base on what sheet is selected in cell A1. So, if I select "Bedroom" from the drop down list, the "Bedroom" sheet, cell B2 shows in Sheet1A2, if I select "Bathroom" then the value of the Bathroom sheet, cell B2 will show in Sheet1A2.

The problem I can't figure out is how to "reference" the sheet name in cell A1 on Sheet 1 based on the number of sheets that I create. IE: If I create "Bedroom" then it doesn't reference BedroomB2 in Sheet1A2 as Bedroom2 Sheet "didn't exist" before hand.

Is there a way to create a formula so that Sheet1A2 references the "sheet name" in Sheet1A1 to pull the data off of Sheet "X" Cell B2 based on the selection in Sheet1A1?

In short, to pull data from another sheet that "exists" one would simply put in ='Sheet2,!B2 but if Sheet "Bathroom" doesn't exist then it won't find the value as there is no reference. Is there a way I can "fill in" the "sheetname" by referencing the selection in Sheet1A1?

So the formula would look something like this for Sheet1A2 . ='Sheet(reference cell A1 text)'!B2

I can't seem to figure it out.

I have also tried "creating a cell" that has the proper "text" for referencing a cell but I can't get it to go into the cell I want it to. IE: I can get a random cell to display 'Bedroom'!B2 but I can't get it with the "=" sign so that it actually references 'Bedroom'1B2 and returns the value.

Hope that all makes sense.

Any thoughts would be appreciated. As an FYI, trying to do this without VBA.

Thanks again

Solved thanks.