using drop down lists to show data

Hi all,

I have a spreadsheet with about 10tabs each showing a detailed profit and loss statement for each division of the company.

I also have a summary sheet the shows a summary of these P&L's with such info as revenue, profit contribution, costs etc.

Now what I want to do is have a drop down list (on the summary tab) that shows all the divisions. When I select a certain division from the list the summary data should read from that sheet.

E.G I select Company A from the drop down list. The Revenue and profit contribution etc should then read from the Company A sheet.

At the moment I have =A1 & "!A40" where A1 contains the list box and A40 is the cell on company A that has the information I want to display.

Any ideas what im doing wrong?

Regards
Gazzr

Hi VBANoob.

Thanks for the reply. I have brushed up on lists and named ranges but still cant get it to work.

For example, I would like to type sheet1 in cell A1. In cell A2 i would have =A1&B1 which would bring back the value in cell B1 in Sheet1.

Thanks
Gazzr

That is perfect! Thanks. Gazzr