Need help with broken formula

Hello,

I need some help with a formula that was fine working on Friday. Over the weekend I created a copy of the master worksheet that the formula was pointing to and cleaned up the data in the new worksheet. Once I was done I reference the new data worksheet and the formula broke. I went back and re-pointed the formula to the original worksheet and that also stayed broken.

What went wrong with the formula?

=SUMIFS({8_26 sheet}, {8_26}, $Resource@row, {Pfizer Team}, [US OA DTC]$2)

When I ask the question in SS AI it tells me that the brackets are incorrect and I should use the following.

=SUMIFS([8_26 sheet], [8_26], [$Resource]@row, [Pfizer Team], [US OA DTC]$2)

I did update one row cell with this recommendation from SS AI and now it's unsupportive.

Thoughts?

Find more posts tagged with

Accepted answers

All comments

Adam Murphy

I would double check that each cross-sheet reference {} is pointing to the correct range/column in the master sheet.

Jason Tarpinian

The square brackets are used when referencing a column name on the sheet itself (needed for any column with a space). The curly brackets are used for referencing a different sheet. Your original formula syntax is correct, so if it isn't working, double check that the reference ranges are correct. Sometimes if you select a column range before the pop-up window loads, it will flash and then only select the first row cell - it's caused a bunch of frustration for me in the past.

Matt Lynn ACT

@Richard Aponte

The AI recommendation is wrong. {} for cross sheet references. [] for same sheet references if it has a space in the column name/string.

If you use the first formula and click edit reference on each one, then it opens the referenced sheet, does each one show the correctly highlighted column? I have had references many times properly reference the sheet, but not retain the column within the sheet, thus it's a dead reference.

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.