I got some help on this a few minutes ago but unfortunately it wasn't exactly what I needed, as I had forgotten about one detail.
On my summary page I have the below formula, with the list of months from D20:O20. The user chooses the month from the dropdown list on the summary page, and that list of months is stored on 'ControlsD55,' as per the formula below.
So I'm hoping to modify this formula so that when the user chooses whichever month they want (there are also 12 worksheets named after each month), the indirect formula goes to the 'ControlsD55' to find which worksheet to get the data from, and from there incorporates the lookup so that the list of months from D20:O20 finds its corresponding month in Row 1 of whichever month file it goes to, while also keeping the sumproduct formula as per below.
Any ideas?
IF(E$19=2,(SUMPRODUCT((INDIRECT(Controls!$D$55&"!A2:A430")=Controls!$B$55)*(INDIRECT(Controls!$D$55&"!B2:B430")="Admin")*(INDIRECT(Controls!$D$55&"!C2:C430"))))
On my summary page I have the below formula, with the list of months from D20:O20. The user chooses the month from the dropdown list on the summary page, and that list of months is stored on 'ControlsD55,' as per the formula below.
So I'm hoping to modify this formula so that when the user chooses whichever month they want (there are also 12 worksheets named after each month), the indirect formula goes to the 'ControlsD55' to find which worksheet to get the data from, and from there incorporates the lookup so that the list of months from D20:O20 finds its corresponding month in Row 1 of whichever month file it goes to, while also keeping the sumproduct formula as per below.
Any ideas?
IF(E$19=2,(SUMPRODUCT((INDIRECT(Controls!$D$55&"!A2:A430")=Controls!$B$55)*(INDIRECT(Controls!$D$55&"!B2:B430")="Admin")*(INDIRECT(Controls!$D$55&"!C2:C430"))))
