Forum Discussion
Solved
Create Sheet that Populates Rows from Multiple Drop Down menus
I have a spreadsheet with five sheets that list projects and whether they are complete or in progress. Each sheet has a list of projects that each have a drop down menu with complete or in progress as the options. How can I create another sheet that populates the rows with all rows from all sheets that have the drop down menu on 'In Progress'? This way I can look at one page and see all the projects that are 'in progress'.
PortCraneTech You wrote "I have a spreadsheet with five sheets that list projects", but you did not mention the names of those five sheets. So I used WkshtAble, WkshtBaker, WkshtCharlie, WkshtDelta, and WkshtEcho as placeholders. Replace those names with the names of your five sheets (and change the "none for" literals accordingly, to something meaningful to you). Note that if your worksheet names include spaces or other special characters, you will have to wrap those names inside apostrophes (e.g., 'Project Sheet 1').
Similarly, change the ranges to be specific to your problem. I hypothesized that columns A thru ZZ were used; change those column references as appropriate. I hypothesized that your column D contained the status; change those column references as appropriate.
PortCraneTech The FILTER function will let you copy a subset of the rows from (one range in) one worksheet, based on a single criterion of "In Progress" (and it would support multiple criteria, but you do not appear to need more than one).
To get subsets from (one range in each of) five worksheets/workbooks, you will probably need a formula that combines at least one FILTER function with a VSTACK function (a better description is here) to put the results together. (There may be exceptions that I won't delve into.)
So let's suppose your existing worksheets contain data in columns A thru ZZ, column headers in row 1, and the project status in column D. (As should be obvious, there is an assumption that all five existing worksheets have the same structure, at least in that portion you want included on the new worksheet.) Your new worksheet could contain "In Progress" in cell A1*, and column headers in row 2. And thus cell A3 of this new worksheet might be:
=VSTACK( FILTER(WkshtAble!A2:ZZ10000,WkshtAble!D2:D10000=A1,"none for Able"), FILTER(WkshtBaker!A2:ZZ10000,WkshtBaker!D2:D10000=A1,"none for Baker"), FILTER(WkshtCharlie!A2:ZZ10000,WkshtCharlie!D2:D10000=A1,"none for Charlie"), FILTER(WkshtDelta!A2:ZZ10000,WkshtDelta!D2:D10000=A1,"none for Delta"), FILTER(WkshtEcho!A2:ZZ10000,WkshtEcho!D2:D10000=A1,"none for Echo") )If you might have more than about 10,000 projects in a spreadsheet, adjust the formula accordingly.
* Putting the status criterion here means you can just change this cell's content to subset a different status, rather than having one worksheet for "In Progress" and another for "Completed" (or any other possible status, like "Proposed", "Pending Funding").
Alternatively you could try a "prettier" formula that does a couple VSTACKS before FILTERing:
=FILTER( VSTACK(WkshtAble!A2:ZZ1000,WkshtBaker!A2:ZZ1000,WkshtCharlie!A2:ZZ1000,WkshtDelta!A2:ZZ1000,WkshtEcho!A2:ZZ1000), VSTACK(WkshtAble!D2:D1000, WkshtBaker!D2:D1000, WkshtCharlie!D2:D1000, WkshtDelta!D2:D1000, WkshtEcho!D2:D1000)=A1, "none" )Note that this formula references only up to 1,000 projects per worksheet; my Excel ran out of resources when I tried the larger range of rows.
SnowMan55 I have done what you suggested but am having problems. When I paste in your formula to cell A3, I get a pop up window 'Update Values:WkshtAble' with the option to open something. I don't know what to do with this? If I just cancel it out, A3 turns to '#REF!'.
What am I missing?
Thanks for your help
PortCraneTech You wrote "I have a spreadsheet with five sheets that list projects", but you did not mention the names of those five sheets. So I used WkshtAble, WkshtBaker, WkshtCharlie, WkshtDelta, and WkshtEcho as placeholders. Replace those names with the names of your five sheets (and change the "none for" literals accordingly, to something meaningful to you). Note that if your worksheet names include spaces or other special characters, you will have to wrap those names inside apostrophes (e.g., 'Project Sheet 1').
Similarly, change the ranges to be specific to your problem. I hypothesized that columns A thru ZZ were used; change those column references as appropriate. I hypothesized that your column D contained the status; change those column references as appropriate.
