May 18 2021 01:35 PM
I have drafted a monthly report which has a main dashboard sheet, along with monthly report sheet (the plan is to add additional sheets as the months progress).
Within the dashboard sheet I have a summary, so every time I select a name from a drop-down it displays key data pulled from the monthly sheet. The function to achieve this looks like this:
=INDEX(April21!E$2:E$36,MATCH(F3,April21!A$2:A$36,0))
So F3 is the drop-down list in my dashboard sheet, and it is pulling data from the April21 sheet.
My question is this: Can I reference multiple drop-down lists within this function? If I select a name from the first drop-down list, then the month from a second drop-down list, it will show the data for that name from that month.
Any assistance would be most appreciated.
May 20 2021 09:49 PM
Hi @PMGlobal
You're talking about dependent dropdowns:
May 21 2021 01:23 AM
As variant, if G3 returns sheet name
=INDEX(INDIRECT("'"&$G$3 & "'!E$2:E$36"),MATCH(F3,INDIRECT("'"&$G$3 & "'!A$2:A$36"),0))
May 21 2021 05:33 AM - edited May 21 2021 05:34 AM
Not really. The lists dont need to be dependent. List 1 = Name, List 2 = Month. So List 2 will remain the same whichever name is chosen.
Rather, I want to use the INDEX and MATCH functions together with 2 separate drop-down lists to pull the same data from a range of worksheets.
I can use INDEX/MATCH with 1 drop-down no worries. So when I select a name from the list it displays the data from Month1 worksheet. Getting it to work with 2 lists is my issue.
I have the main worksheet (with the 2 lists, and summary data), then several other worksheets named by month (these all have the same table layout, but different data sets)
So when I select a name AND a month from the 2 lists, I want it to show the data for that month (for the named person).
I have attached a copy of the file below if it helps.
May 21 2021 08:17 AM
May 21 2021 08:34 AM