Worksheet chooser

[ryantaylor]

I have set up a workbook in which each worksheet contains identical information for each employee of my business, mainly achievement against targets, income earned etc. etc. Ideally I would like to summarise the key elements of each of these in a dashboard style format on the first sheet of my workbook so that I can see the most important data at a glance. Instead of creating a separate dashboard for each employee I would ideally like to be able to choose the employee's name from a drop down list and the sheet automatically update to the correct source data dependant on the name chosen. Is this possible?

[Palmetto]

You can use the INDIRECT function to accomplish this.

If the sheets tabs are named for each employee and the drop down list contains a list of the sheet tabs/employee names, then

If cell A1 on the summary sheet is the drop down:

=INDIRECT(A1&"!A1")

would return the value of cell A1 on the sheet selected in the drop down.
This should get you started.

[ryantaylor]

Thank you so much for your response, it's good to know that this is actually possible, however when I type out the formula you suggest, Excel throws it out saying there's a syntax error. I've tried some feeble attempts at workarounds but not getting very far.

[ryantaylor]

I've got it now, there was a " missing before the closing parenthesis - many thanks!

[DonkeyOte]

Also - FWIW - when using INDIRECT with variable sheet reference it's generally a good idea to encase sheet name within ' ' by default:

Please Login or Register  to view this content.