There has to be an easier way...

Hi smarties, I have 40 metric sheets (one per location). In each sheet I have an countif formula that references a data sheet. I have this formula in about 20 columns. A sample is in the screen shot. It says something like count if the name in the Frontline leader column of the metric sheet matches the name in the data entry sheet and the date in the Week column is 3 weeks ago, look in the Hazard and Injury Columns and if it says Yes, count as 1 if not, count as 0 then divide by same criteria but by total yes or no (so if it's yes in Hazard and No in Injury for that leader for that week, it comes up 50%)

I repeat this formula for 13 weeks, but instead of -1 I change the number to how many weeks ago (times 13)

Every time I make a change I have to change 40 sheetsx20 columns x 13 weeks then drag down to the 20 cells in that week's set of cells. That's 10,400 manual cell updates which is not optimal.

I know we can't copy a formula from one sheet to another. Is there any easier way?

Image of formula above and in text below: note the formula is fine.

=IF([Frontline Leader]@row = "", "", IFERROR(SUM(COUNTIFS({Who}, [Frontline Leader]@row, {Hazard}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), COUNTIFS({Who}, [Frontline Leader]@row, {Injuries}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) / SUM(COUNTIFS({Who}, [Frontline Leader]@row, {Hazard}, OR(@cell = "Yes", @cell = "No"), {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), COUNTIFS({Who}, [Frontline Leader]@row, {Injuries}, OR(@cell = "Yes", @cell = "No"), {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))), ""))

Find more posts tagged with

Formulas

References

Accepted answers

bisaacs

Hey @EEK,

I did a little more digging, and the idea I had in my head (having a master reference formula that the rest of the sheets could reference) isn't possible (at least as of 2022):

Absolute Reference Cells — Smartsheet Community

So that was my fault for not looking into this a little further!

After doing a bit of digging I'm not seeing anything that would make doing a mass formula update easier unfortunately. Hopefully someone else can step in and provide a solution that neither of us thought about!

All comments

bisaacs

Hey @EEK,

Could you create a helper sheet where you copy/paste this formula, then have all the cells where this formula is currently stored just reference the helper sheet? Then you'd only have to change one cell and it should update all the other sheets

EEK

I love this idea. I don't think it will work because the references have the same names, but are referencing a specific data entry sheet.

I create the template metric sheet that references a data entry sheet. Then I save as new, referencing the newly created sheets.

bisaacs

Hey @EEK,

Hmmm, what about creating a helper column in each sheet and the formula is stored in that column, then each sheet just references the helper column? Not as efficient as the first option, but it should work (and only require you to update 40 cells vs over 10k!

EEK

Thanks @bisaacs Are you saying I could put the formula in a master formula spreadsheet and then in the metric sheet for each location have a helper column that has an index match so it appears as text (no = sign)? or is the formula in there and updates in this way? When I think I've done it the other way I think the values enter in, but not the formula itself.

bisaacs

Hey @EEK,

I did a little more digging, and the idea I had in my head (having a master reference formula that the rest of the sheets could reference) isn't possible (at least as of 2022):

Absolute Reference Cells — Smartsheet Community

So that was my fault for not looking into this a little further!

EEK

@bisaacs I appreciate you digging! I know a suggestion to Smartsheet, and that this is my option. That noted, I can perhaps get some help so I'm not doing it all. Thanks!

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.