cross sheet references from 1 sheet to 20 sheets

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
    Julie Fortney Overachievers

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!