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.

[Pete_UK]

In the 3 formulae you quote near the end of your post, you can't shorten the INDIRECT term in the way you show. You also have some square brackets, which are usually reserved to enclose filenames when referring to external files. You will have to show separate INDIRECT terms for each range reference that you want to use, like this:

=COUNT(INDIRECT("'"&C4&"'!F:F"),INDIRECT("'"&C5&"'!B:B"))

which will count numerics in the ranges F:F of the sheet given in C4 and in B:B of the sheet given in C5. I'm not sure what you are trying to do with either of the COUNTIFS formulae, as there doesn't seem to be any condition.

Hope this helps.

Pete

[IquikDraw]

The last 2, the COUNTIF and COUNTIFS reference were weak frustration attempts on my part to modify what I thought would work to get the results. Those didn't fault but still didn't modify any data by way of the second drop down either. I thought a saw a website where I saw the square brackets used on the second part of the formula so I frustratingly tried that too, without researching first. I appreciate the feedback and I will try your solution.

[IquikDraw]

=COUNT(INDIRECT("'"&C5&"'!F:F"),INDIRECT("'"&C6&"'!B:B"))

This isn't working. This idea of this formula, or what I'm trying to make happen, is to combine the results of selecting the given worksheet from the first drop down list and then filter it by the warehouse selected from the 2nd drop down list.

With the above formula in the "Total Shipped" Box, the total shipped still stays the grand total of all rows from the worksheet selected from the first drop down list (From C5 - worksheet/tab select), regardless of what I select in the second drop down list (C6 - Warehouse select). The second filter does not modify the result to only show the shipments for the selected warehouse.

Would something like adding =COUNT(103,INDIRECT("'"&C5&"'!F:F"),INDIRECT("'"&C6&"'!B:B")) Work? to show me unhidden cells only?

[IquikDraw]

I tried:

=COUNT(INDIRECT("'"&C5&"'!F:F"),INDIRECT(103,"'"&C6&"'!B:B"))

That stays the same, only shows the grand total for all rows (But also doe not cause errors.). NO filtering by way of the 2nd drop down list.

[IquikDraw]

This finally worked:

=COUNTIFS(INDIRECT(C5 & "!F:F"),1,INDIRECT(C5 & "!B:B"),C6)

I needed to change the "COUNT" to "COUNTIFS" to handle multiple referenced drop downs...and...put in a variable to set the condition as true or false...that's the ",1, between the INDIRECT references. For the late ship counts, I used the same formula with the variable changed to ",0," and the third cell adds both together for the total. All working great at this point. I appreciate that the formula you provided was correct overall for what I needed. Thank you for setting me on the path.