Formula-Reference another sheet and see the column rather than(SheetName Range #)within the formula

Hello,

I have a formula I am using on one sheet. The sheet is named "Mark" since he is the Point Person.

=COUNTIFS([First Contact Year]:[First Contact Year], "2020", [First Contact Month]:[First Contact Month], "Jan", [Point Person]:[Point Person], "Mark")

I have another sheet named "Summary - 2020". I want this exact formula to populate a box on this Summary - 2020 sheet. When I make the formula on the Summary - 2020 sheet, I have to use the "reference another sheet" link and it pretty much creates the formula for me (which is the same as above) but it looks like this:

=COUNTIFS({Mark Range 5}, "2020", {Mark Range 3}, "Jan", {Mark Range 4}, "Mark")

I have 10 other people I am doing this for and I need to manually create these "Mark Range 5" but I would rather just use the above formula and change the name Mark to Adam, Tracy, etc. depending on the sheet name. I would think it would look something like this but it doesn't work:

=COUNTIFS({Mark [First Contact Year]:[First Contact Year]}, "2020", {Mark [First Contact Month]:[First Contact Month]}, "Jan", {Mark [Point Person]:[Point Person]}, "Mark")

I want to see that I am referencing First Contact Year column rather than Mark Range 5 within the formula. Please help!

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

functionality

Accepted answers

Genevieve P.

Hi John,

The ranges names (Range 3, Range 4, etc) are default names added in when you create a cross-sheet reference but don't manually create a specific name for it. You can edit these by doing the following:

Open up the formula and click in the middle of the first range.
Click on the blue "Edit Reference" text in the pop-up box
In the new window, look up at the top-left where the Sheet reference name is displayed (this is to the right of where you can search for a sheet name)
Change this, then click Update the reference

That said, I would suggest updating the reference name to be something with only text. This way you won't have extra characters in the reference that could confuse the formula. Try something like: {Mark's Sheet - First Contact Year Column}

Here's an example of how you could have this formula:

=COUNTIFS({Mark's Sheet - First Contact Year Column}, "2020", {Mark's Sheet - First Contact Month Column}, "Jan", {Mark's Sheet - Point Person}, "Mark")

Keep in mind that this is how you update the name of a current reference. To insert a new reference (like for a new sheet), you will want to delete out the current {Mark Ranges}, then click in that spot and choose Reference Another Sheet (instead of Edit).

Here's a Help Center article that goes over cross-sheet references:

https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

Let me know if you have any further questions about this, or if I have misunderstood the question!

Cheers,

Genevieve

All comments

Genevieve P.

Hi John,

Open up the formula and click in the middle of the first range.
Click on the blue "Edit Reference" text in the pop-up box
In the new window, look up at the top-left where the Sheet reference name is displayed (this is to the right of where you can search for a sheet name)
Change this, then click Update the reference

Here's an example of how you could have this formula:

=COUNTIFS({Mark's Sheet - First Contact Year Column}, "2020", {Mark's Sheet - First Contact Month Column}, "Jan", {Mark's Sheet - Point Person}, "Mark")

Here's a Help Center article that goes over cross-sheet references:

https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

Let me know if you have any further questions about this, or if I have misunderstood the question!

Cheers,

Genevieve

John Morgenthaler

Thanks Genevieve. This is helpful. The link you provided does mention *Each sheet can include no more than 100 distinct cross-sheet references.

Say I use the same formula but change the year from 2020 to 2019. Do you know if this would be considered 1 or 2 distinct cross-sheet references?

=COUNTIFS({Mark's Sheet - First Contact Year Column}, "2019", {Mark's Sheet - First Contact Month Column}, "Jan", {Mark's Sheet - Point Person}, "Mark")

=COUNTIFS({Mark's Sheet - First Contact Year Column}, "2020", {Mark's Sheet - First Contact Month Column}, "Jan", {Mark's Sheet - Point Person}, "Mark")

Genevieve P.

Hi John,

Actually, there are 3 distinct references:

{Mark's Sheet - First Contact Year Column}
{Mark's Sheet - First Contact Month Column}
{Mark's Sheet - Point Person}

These are three separate columns referenced in your formula, which makes it 3 distinct references. That said, when you change the year to 2020 or anything else, you're not changing what columns are being referenced. That means that you still only have 3.

Here's another Community post that discusses this:

https://community.smartsheet.com/discussion/comment/240223#Comment_240223

Cheers,

Genevieve

John Morgenthaler

Thanks!

Genevieve P.

No problem!

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.