I've been getting frustrated by cross sheet references recently.
The other day I added cross sheet references I knew existed into a new formula in a cell only to get the INVALID REF# error. I went into my reference manager and yup, the reference did exist. After wasting a few hours poking around I found that if I highlighted {reference} with my cursor, clicked 'reference another sheet' and then reselected the appropriate column, it would work.
Make no sense, but whatever.
Today I've run into 'unable to create cross-sheet reference, because this sheet already contains the maximum number of different cross-sheet references."
Hmmm. I know I'm not close to 100, but I am using an old template so maybe there are some redundant references in there, I'll check the reference manager... 55.
Again, makes no sense, say bye bye to a few more hours trying to figure this one out. Thanks!!
Anyone got any ideas?
Hello,
This is part of a larger issue with Smartsheet functionality at the moment. I've reached out to support about the cross sheet references not updating and got this response.
I've found a workaround that has kept my sheets functional. In Sheet Reference Manager, filter to the unused references and use the 3 dots on the side to edit them.
You can change the data source to the new sheet and range that you want to reference. The important thing is to not change the name of the Sheet Reference Name. This will mean you will have Sheet A Range 1 pulling data from Sheet B. Copy the name of the range, click Update Reference, and use it as normal.
Well, it just gets more silly doesn't it!
What I didn't mention above was that I deleted a few redundant references in the sheet before I started working on it, or at least I thought I did!
If I close the sheet, then reopen it, all the deleted references get reloaded into the reference manager and I have 101 references, which doesn't make sense at all but at least I know where the error message is coming from (kind of).
OK, I'll delete them again!
Deletes references, checks reference manager (55), tries to build a new reference:
"unable to create cross-sheet reference, because this sheet already contains the maximum number of different cross-sheet references."
Closes sheet, opens sheet, checks reference manager, 101 references. Rinse and repeat three times whilst swearing to myself.
Does anyone know what causes this stupid behaviour? Does anyone know how I can work around it?
Thanks!!
Did you see this thread?
I hope @Lucas Rayala ’s solution works for you.
its not a solution, its just wrong.
Hello,
This is part of a larger issue with Smartsheet functionality at the moment. I've reached out to support about the cross sheet references not updating and got this response.
I've found a workaround that has kept my sheets functional. In Sheet Reference Manager, filter to the unused references and use the 3 dots on the side to edit them.
You can change the data source to the new sheet and range that you want to reference. The important thing is to not change the name of the Sheet Reference Name. This will mean you will have Sheet A Range 1 pulling data from Sheet B. Copy the name of the range, click Update Reference, and use it as normal.
Thanks SavB.!
That's a great answer!
I contemplated contacting support, but didn't have a week to wait for a response. I wasn't that far into building my sheet and opted to just start again, but assuming that it still keeps total references under 100 (which it wouldn't have in my case), that's a great work around.
