cross sheet references from 1 sheet to 20 sheets

Bobby1

the current Cross sheet formula below is 1 sheet, due to an error message of " to many references" i split 3 years into quartley sheets ex" 2020 Q1/2020 Q2--2021 Q1/2022---2022 Q1/2022 Q2 ..ETC

I need a formula that will cross reference all the Quarterly sheets ex: {Q1 2022 agrmt Type}/{Q2 2022 agrmt Type}/{Q1 2021 agrmt Type}/{Q2 2021 agrmt Type}...

=IF(Vendor@row = "", "", IF([Agrmt 1.]@row = "Sett", SUMIFS({9.20. INV 2022 total INV $}, {9.20. INV 2022 Payee}, Vendor@row, {9.20. INV 2022 agrmt Type}, "Sett"), SUMIF({9.20. INV 2022 agrmnt #}, [Agrmt No.]@row, {9.20. INV 2022 total INV $})))

Julie Fortney

Hi @Bobby1

I can think of two options:

• Add the sumifs and sumif formulas for the other sheets to your formula.

=IF(Vendor@row = "", "", IF([Agrmt 1.]@row = "Sett", SUMIFS({9.20. INV 2022 total INV $}, {9.20. INV 2022 Payee}, Vendor@row, {9.20. INV 2022 agrmt Type}, "Sett") + SUMIFS(Sheet 2 reference and criteria) + SUMIFS(Sheet 3 reference and criteria)..., SUMIF({9.20. INV 2022 agrmnt #}, [Agrmt No.]@row, {9.20. INV 2022 total INV $})))

• Add helper columns to pull data from each sheet individually. This could be helpful for troubleshooting, or if you want to reference your values per quarter.

I hope that helps.

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt