lost_in_the_sauce
Board Regular
- Joined
- Jan 18, 2021
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!$N:$N, 'datasheet.....
but as I am creating a dashboard I would like to have the first variable ($N:$N) pull from a drop down list. I've created a drop down that does months and am trying to get the first variable to index match a table based on the drop down value, i.e. if it selects "November" the the INDEX MATCH will fill $N:$N for the SUMIFS formula but I'm getting errors with the placement of the apostrophe.
Old working formula that pulls only a predesignated column:
=SUMIFS('data1'!$N:$N, 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
Nonworking formula I am trying for the first SUMIFS variable to select via INDEX MATCH to a drop down cell value
=SUMIFS('data1'!(INDEX(Sheet2!D14:D25,match(Sheet1!M2,Sheet2!B14:B25,0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
where Sheet1M2 is the drop down box and Sheet2 D14:D25 and B14:B25 are the array I'm trying to reference via drop down box
but as I am creating a dashboard I would like to have the first variable ($N:$N) pull from a drop down list. I've created a drop down that does months and am trying to get the first variable to index match a table based on the drop down value, i.e. if it selects "November" the the INDEX MATCH will fill $N:$N for the SUMIFS formula but I'm getting errors with the placement of the apostrophe.
Old working formula that pulls only a predesignated column:
=SUMIFS('data1'!$N:$N, 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
Nonworking formula I am trying for the first SUMIFS variable to select via INDEX MATCH to a drop down cell value
=SUMIFS('data1'!(INDEX(Sheet2!D14:D25,match(Sheet1!M2,Sheet2!B14:B25,0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
where Sheet1M2 is the drop down box and Sheet2 D14:D25 and B14:B25 are the array I'm trying to reference via drop down box