variable sheet reference

Have a file with several worksheets. Let's say the sheet names are AAA, BBB, CCC, DDD. I have another sheet (let's say Sheet1) that I want to add charts from data on one of these sheets, depending upon a drop down choice of the sheet name. So I would have a drop down choice of AAA, BBB, CCC, DDD. When I choose one of these, I would like the chart to reference the data on that particular sheet.

Knowing that the reference to cell W67 on sheet BBB would be = BBB!W67, is there any way to make the BBB reference in that formula variable? So if I had cell A1 in Sheet1 as my drop down selection and BBB was selected now as the value in cell A1, is there a certain syntax to the formula to that BBB!W7 can be referenced to the sheet selected in A1?

Make sense? Possible? How?

Many thanks.

Try

=INDIRECT("'"&A1&"'!W7")