How do I add a drop down list of strings that represent different worksheet names in Excel 2007?

I have recently been placed on a reporting project for my job. My overall objective is to make a report of 9 different charts that is able to filter the data by date and also by group. There are 22 different groups and I need to drill down to show the report by individual month, week, and day. I have to do this all in excel 2007. For now, I'm working on showing results by month and for all the different applications groups plus a summary for all groups.

The way that I have set this up is, to have one workbook that contains 2 worksheets, 1 that has all 9 graphs, let's call this Dashboard_All. These graphs are linked to the second sheet that contains all of the information for each graph, let's call this Chart_Data.

You may be wondering, where this Chart Data is coming from? Chart_Data references some tables in another workbook that contains raw data, pivot tables, and matrix tables. I have it set up to that one worksheet here contains all the raw data in one table. One worksheet has all the pivot tables (1 pivot table for each chart) in order to sift through the raw data and give me the results. I have made 23 different worksheet that have these results, 1 for each group (I used the group filter on each pivot table to do this) and 1 for all groups as a summary. I also made 23 different worksheets where I have made matrix tables for each of the pivot tables results. These tables are used because when ever I change a pivot table for a different group, the results change and sometimes columns or rows are omitted if there is no data there to be read. So I made tables that reference the pivot tables in order to have cells that basically act as spacers in case there are blank values in the pivot tables. These charts have all the possible combinations for the different months as rows and the different columns. Chart_data from the previous workbook references these matrix tables.

I used a VLOOKUP function in Chart_Data that is linked to a drop down list. For example, Please, choose a month: drop down January February March ... All Months. The Vlookup knows that when I select a month to only display the various data for the charts only for that respective month. So my monthly objective has been accomplished. Now for the application group, I need another drop down box that will reference the different matrix table worksheets in my workbook. If I choose a group from the drop down, the VLOOKUP will know to go to that respective sheet with the appropriate information.

The problem is, how do I get it to recognize different sheets? For example,

=VLOOKUP(C3, '[BlahBlah_WaWa_Cha开发者_如何学JAVArts.xlsx]Group 1 Chart Data'!$A$20:$B$32, 2)

How do I have it so that "Group 1" changes with the drop down selection?

I know this is long winded and confusing, but I'm not sure how else to explain this. Thanks alot.

---

You can use INDIRECT() Eg;

=VLOOKUP(C3,INDIRECT("[HPSM_" & C1  & ".xlsx]Sheet1!$A$4:$B$16"),2)

---

It isn't clear at all what you're trying to do or how your content is structured.

Are you making one set of charts for each of the 22 groups, or one set of charts for ALL 22 groups?

Are the charts in one stand-alone workbook or in each of the 22 group workbooks?

Are you attempting to retrieve the workbook names from the stand-alone workbook or within the group workbooks?

...etc...

Please try re-writing your question/explanation to clarify how your data is structured and what your objectives are.