Display a specific table based on multiple drop down variables

I am looking to create an excel report that can find and display a specific table out of several tables based on three different variables. The source tables would be populated manually and therefore the issue is how to get a first sheet of the excel to show a specific table.

In order to clarify, what I want to create:

- I want to see the hourly sales of a specific business unit on a specific day of a specific week.
- The tables would be built so that we have the opening hours on the rows and the sales figures on a specific column.
- The source data would include a table for instance Business Unit A on Monday and on a Week 24 (As you can imagine there would be dozens of tables in the source data).
- I want to fetch that specific table from the large number of source tables by selecting variables in three dropdown menus, first for Business Unit, second for Weekday and third for Week Number
- Only upon selecting all three variables from the three dropdown menus would a table be fetched from the several different source tables and displayed on the first sheet under the dropdown menus


How would I need to go about creating something like this? Please help excel gurus.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

It's really easy to combine and report on the data from multiple tables using Power Query. In your case, you need to do a little work to prepare the tables, first of all.

This VBA code will rename each table of source data, based on the Business Unit / Weekday / Week Number data above the table, then it will compile the list of new table names into another table:

Please Login or Register  to view this content.

Now we can use Power Query to combine the data from the source tables, and use this combined data as a data model source, for the consolidated report:

Please Login or Register  to view this content.

Finally, just insert a pivot table onto the Report sheet, with filters on Business Unit, Weekday and Week Number.

See attachment for worked example.

Have a look at the video in my signature, for how to use the provided Power Query code in your workbook.

We needed to compile a list of tables for Power Query to both loop through, and get the business unit / weekday / week number data from (as that data is not in the tables.) That's what the VBA code does - it loops through all the "Branch" worksheets, renames each table to include the business unit / weekday / week number data, and adds the (new) table name to a table on hidden worksheet "Parameters".

Power Query looks at the table on hidden sheet "Parameters" to get the list of table names, then combines data from each of those tables.

So if you're using a different sheet / table structure, run the VBA code first, to recompile the TableList table on the "Parameters" sheet.

Make sense?