Referencing one sheet with date ranges to change all SUMIFS and COUNTIFS at once

Hello! Every month we update multiple project items once the previous month of data is collected. Up until now, we have gone into each projects metric sheet to update, changing the date range to pull the recent data.

Instead, I would like to build one reference sheet, populated with every day of the year in 12 month columns, so that by simply changing a month pointer on a metric sheet, it will find the range of days for that month on the reference sheet and change all the formulae on that sheet, no matter what other criteria is in that formula.

I've tinkered with the INDEX/MATCH functions, but the ranges for the other items vary from sheet to sheet so I'd still have to go into each project to get them to fire correctly.

First time question. I've used the Community forums to solve other problems and this place is a great resource for solutions, so thanks! Apologies if this was asked in another thread.

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi @Jeff Widrig

I hope you're well and safe!

Here's a possible workaround or workarounds

Create a Report showing the Dates from each Metric Sheet and change them there.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jeff Widrig

Andrée, thank you for the suggestion! I've not found a way to change data displayed on a report without changing it on the original sheet. How does that work? I thought a report was just one-way, to filter and display a sheet for other users or on a Dashboard.

Even with that, it still seems like the same amount of work required to change all the source sheets individually. What I'm looking for is a piece of formula that I can use universally that will do the following:

I import an Excel file with the last month of data and add it to the Master sheet using 'Copy to Another Sheet'. It includes a column that is set as a Date column type. I then go into the main metric sheet for that project and change one cell. When that cell changes (exa. from April to May) the formula on the sheet that use that cell as a criterion will look to the month reference sheet and, instead of looking for

FIND([Primary Column]5, @cell) > 0, {Reference Date}, >=DATE(2022, 4, 1), {Reference Date}, <=DATE(2022, 4, 31))

...it will instead look for

FIND([Primary Column]5, @cell) > 0, {Reference Date}, >=DATE(2022, 5, 1), {Reference Date}, <=DATE(2022, 5, 31))

and find all the sums with the new month that match the other criteria. This changes the Sum, which updates to a Dashboard metric or chart widget.

Is this possible? Thanks again for the response!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.