Cross Sheet Reference Issue - Renamed Sheet Reference

John Crick

Greetings,

I am running into an issue with a cross-sheet reference in a Max Collect formula. Below is the intended formula:

=MAX(COLLECT({Vision Media 2023 Customer Billing Tracker Range 2}, {Vision Media 2023 Customer Billing Tracker Range 1}, "Fulfillment - Physical", {Vision Media 2023 Customer Billing Tracker Range 2}, [Client ID (NAV ID)]@row))

Below is the formula returned after its creation ("2023" of the final cross-sheet reference is changed to "2022"):

=MAX(COLLECT({Vision Media 2023 Customer Billing Tracker Range 2}, {Vision Media 2023 Customer Billing Tracker Range 1}, "Fulfillment - Physical", {Vision Media 2022 Customer Billing Tracker Range 2}, [Client ID (NAV ID)]@row))

I have rewritten this formula multiple times, each time cross-referencing the same "2023" Smartsheet. But every time I make the final cross-reference, the formula shows "2022".

What would cause the change in the cross-reference and how can it be resolved? Do cross-reference limits play a part? If so, how?

Thank you for assisting me in resolving this issue.

John

Paul Newcome

I have had the same issue when updating the name of a cross sheet reference in a column formula. I have found that generally speaking it tends to work more reliably when I convert to a cell formula, update the name, then convert back to a column formula.

Is it at least referencing the correct sheet and just not updating the name or is it reverting back to another sheet entirely?

John Crick

Paul,

Good morning. Thank you for the suggestion. When looking at this more closely I found the issue: even though I was selecting the 2023 Smartsheet, the reference name showed it as 2022. So yes, it was pulling from the correct sheet. I changed the name of the referenced sheet to 2023 so that it was visibly clear that the right data was being pulled.

John

Paul Newcome

Yes. If you edit a reference and change the sheet, the name of the reference also needs to be manually updated. Glad you were able to get it sorted.