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 $})))

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.