CODEWITHSUNDEEP
google-sheets
Jey Jay
Jey Jay

Reputation: 113

Google Sheets: Repeat an INDEX function within an Arrayformula function

I'm trying to repeat an Index function inside an ArrayFormula function and I have two problems:

  1. The sum is done but I also have other results which I don't know where they come from (starting from the column P)

  2. I can't figure out how to automatically take all the sheets that are listed in column A. For example, if I add a new sheet - let's say Client <200 in A4 with the sheet and all the data - I need to add the INDEX part in the ArrayFomula

    =Arrayformula(INDEX(INDIRECT($A$1&"!$B"&match(C4,INDIRECT($A$1&"!A:A"),0)&":"&match(C4,INDIRECT($A$1&"!A:A"),0)))+INDEX(INDIRECT($A$2&"!$B"&match(C4,INDIRECT($A$2&"!A:A"),0)&":"&match(C4,INDIRECT($A$2&"!A:A"),0)))+INDEX(INDIRECT($A$3&"!$B"&match(C4,INDIRECT($A$3&"!A:A"),0)&":"&match(C4,INDIRECT($A$3&"!A:A"),0))))

The spreadsheet can be viewed here: https://docs.google.com/spreadsheets/d/1yBC4oQuhKOkIkf3lQZGBKcZQ3YjU_5N6MCIJZzdt0O4/edit?usp=sharing

Thank you!

Upvotes: 0

Views: 209

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(INDIRECT(A1&"!B2:M4")+
              INDIRECT(A2&"!B2:M4")+
              INDIRECT(A3&"!B2:M4"))

enter image description here

to count in missing/future sheets you can do:

=ARRAYFORMULA(INDIRECT(A1&"!B2:M4")+
              INDIRECT(A2&"!B2:M4")+
              INDIRECT(A3&"!B2:M4")+
              IF(A4<>"", INDIRECT(A4&"!B2:M4"), SPLIT(REPT("01", 12), 1))+
              IF(A5<>"", INDIRECT(A5&"!B2:M4"), SPLIT(REPT("01", 12), 1)))

enter image description here

Upvotes: 1

Related Questions