Combining Drop down lists to filer data from a separate tab...How?

[IquikDraw]

So, I hope this does not get to wordy.

I am not new to Excel at all. I am new to VBA. For this question, my problem points to normal Excel functionality, not VBA. I am posting a stripped down example file. Some of the automation may not work since I cut pieces, and extra tabs out to simplify the issue.

My boss asked me to create a spreadsheet wherein we could (Any user assigned to update this.) track late shipments by comparing actual ship dates to promise dates and be able to select the specific week to focus on, and select the warehouse involved. With much help from recorded macros I then streamlined or improved from hours of website reading and many commented notes to myself. The way my VBA works...We have an online EPR system that has this information. We pull the report out of that system, save to a specific named Excel file. When you click an update button in my file, the report gets imported to a master data table, adds a column where a formula determines if a given row is late or on time, and a column for reason codes to be entered for charting purposes. a new tab/worksheet gets created after asking the user to type in the new tab name. on a hidden summary data page, I collect the tab names in a dynamic list as well as the list of warehouses that are present on a given weekly tab. Then on the front (Charts) page, I have a small data table that will (By way of normal Excel formulas) show the total shipped, the total on time, the total late, and on down, the breakout of reason codes to populate the charts (As well.). I think it was necessary to explain all that.

Now, I have a drop down list on the front page where the user can select the "Shipping week" coming from the hidden list but all of my data table references "INDIRECT" the information on the focus tab for calculation purposes. So now, my problem is...let's say for example, that way I am grabbing the required data from the selected tab is by using and another example for another value I am calculating:

=COUNT(INDIRECT("'"&C4&"'!F:F")) - For total

=(COUNTIF(INDIRECT("'"&C4&"'!F:F"),0)) - For total late

So, these formulas use INDIRECT to take what is selected in the tab list drop down, goes out to that selected sheet, and pulls in what I am asking it to provide, in this case the total number of shipments on the tab selected in the drop down, like this (I know, image, example file is here too...):

Capture.JPG

Capture1.JPG

Now, I want to (Secondary) be able to also filter (From this same front page.) the information by way of yet another drop down list that will filter the results warehouse I select, or all of them if that's the case. So far I have tried:

=(COUNT(INDIRECT("'"&C4&"'!F:F"),["'"&C5&"'!B:B"])
=(COUNTIF(INDIRECT("'"&C4&"'!F:F"),["'"&C5&"'!B:B"])
=(COUNTIFS(INDIRECT("'"&C4&"'!F:F"),["'"&C5&"'!B:B"])

In an attempt to combine the selections from both drop down lists to filter the results by both tab (Dropdown #1) AND warehouse selected (Dropdown #2). I don't know if this just can't be done this way, or, I have syntax errors stopping me or what but I just cannot get the secondary filter working from the front page drop down list(s). The example file should have enough information to play with this relative to the one tab I left in it, the original has many tabs.

Capture2.JPG


Total frustration getting this working. I'm not sure how to go into "Cascading" or "Dependent" drop downs per say, as I just barely started researching that...maybe that's the easy answer I am missing trying. Sorry it's windy, just wanted people to understand the big picture what I'm doing so it all fits.

Thank you for any help you can throw at this issue.