Dynamic Tab linking in Google Sheets

0
votes

Hello I am trying to set up dynamic formulas that will reference my technicians "tabs" on my google sheets doc.

=SUMIFS(Antonio!E2:E, Antonio!A2:A, ">="&N1, Antonio!A2:A, "<="&O1) + SUMIFS(Antonio!F2:F, Antonio!A2:A, ">="&N1, Antonio!A2:A, "<="&O1) + SUMIFS(Antonio!G2:G, Antonio!A2:A, ">="&N1, Antonio!A2:A, "<="&O1) + SUMIFS(Antonio!H2:H, Antonio!A2:A, ">="&N1, Antonio!A2:A, "<="&O1)+ SUMIFS(Antonio!I2:I, Antonio!A2:A, ">="&N1, Antonio!A2:A, "<="&O1)

I'd like the formula to read something like " =sumifs(M3&!E2:E.... " with M3 being the reference cell that has Antonio (or another tech's name).

Any help is greatly appreciated! Thank you!

1
google-sheetsgoogle-sheets-formula
see: stackoverflow.com/a/57921075/5632629 – player0
Hey Player0 thanks for responding but I'm not sure how I would implement that in my formula, my formula is already super long, and having to add a text join to every part of that would be super cumbersome and I'm not even sure how I would do it. – Alan M

1 Answers

1
votes

Given your example:

=SUM(FILTER(INDIRECT(M3&"!E2:I"),INDIRECT(M3&"!A2:A")>=N1,INDIRECT(M3&"!A2:A")<=O1))

The INDIRECT function essentially lets you build a text string to serve as a live range.

FILTER allows you to isolate only those parts of a range that match all listed criteria.

SUM would add just those matches from the FILTER.