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?

Best Answer


  • Pamb10
    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.
    Genevieve P. Employee Admin

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

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

  • Pamb10
    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.
    Genevieve P. Employee Admin

    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:

    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.