Cross sheet formula reference's not working as expected

This problem is going to be really difficult to explain but here goes it.....

I have a sheet with Jira ticket information, including Creation Date and Resolution date. In other sheet I am doing calculations using these two columns to capture the correct data.

While creating the formula, I click Reference Another Sheet to select the column to calculate with. The Jira Tickets sheet is displayed. In this instance, I am looking for the Resolution Date column.

When i click the column, I notice that the Sheet Reference name does not match the column I chose. It actually says Creation Date. If I click on the Creation Date column, the Sheet reference name says Resolution Date.

When I click Insert Reference, the correct data is used but the incorrect naming is used for the column....very confusing. Not really sure how I got in this state. How can I correct this?

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Pamb10

It looks like the references were just labelled a bit backwards, which is easy to fix!

In one of the formulas that you use this reference in, click on the referenced name and in the window that pops up choose "Edit Reference".

This will bring you to the window where the column is selected, like in your screen capture. In this window, change the Sheet Reference Name (that you have in the red box) to be the correct title. These names are manually created by yourself, so you can edit them at any time.

Once you have changed the name, you will see that the blue button on the bottom right corner of this window changes to "Update Reference". Click this and all instances of this specific reference will have a new name throughout your whole sheet.

You can do this for both references and adjust the name to be the correct one, however you can't name them the same title in the transition. You may want to title them backwards (for example, Ticket Creation Date JIRA) so that you can tell them apart.

You may also want to check out our Help Center Article on Cross Sheet References (click here).

Let me know if you have any questions about this!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Pamb10

It looks like the references were just labelled a bit backwards, which is easy to fix!

In one of the formulas that you use this reference in, click on the referenced name and in the window that pops up choose "Edit Reference".

You may also want to check out our Help Center Article on Cross Sheet References (click here).

Let me know if you have any questions about this!

Cheers,

Genevieve

Pamb10

Thank you Genevieve. Much appreciated. I did end up doing that, although I have no idea how it happened in the first place.

However in doing this, I ended up with bad formulas. I will post in a new thread later on.

Genevieve P.

I'm glad you were able to sort out this initial issue!

I'll keep my eyes out for your next question then. :)

Pamb10

As a followup, im trying to determine why one sheet with calculations is working and another sheet with similar calculations is not. They are both using the same underlying sheet (created via Jira connector).

Are the Sheet reference names for each column exclusive to the sheet that is referencing the column?

For example, in my 2020 calculation, the resolution column is referenced as {JIRA Tickets Resolution} and upon checking it, it does point to the correct Resolution Date column.

In my 2019 calculation (a different sheet), the resolution column is referenced as {JIRA Tickets Resolution Date1} which also points to the correct Resolution Date column (the same one as above).

Genevieve P.

Hi @Pamb10

References are unique to the sheet they’re created on and can’t be used on other sheets without recreating them.

For example, if you copied and pasted the words {JIRA Tickets Resolution} from your 2020 sheet into your second, 2019 sheet, the formula wouldn't know what range you want to reference at all. You would need to insert a new Cross-Sheet reference by clicking on the blue "Reference Another Sheet" link in the pop-up window. Then you can title this column/reference whatever you would like. It can be titled differently than what you have in the other sheet.

The article on Cross-Sheet references has a lot of tips and tricks like this listed: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

If your formula is not working correctly, it would be useful to see screen captures of your sheet in Grid view, the reference open (like your first picture), and a description of what you are looking to do.

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.