Problem with #invalidrefs
I have built a datasheet, which pulls data from a source sheet ("PD Database"). I added over a hundred formula to the datasheet, along the lines of:
=SUMIFS({PD database Range 5}, {PD Database Range 4}, "PL", {PD Database Range 3}, "No")
The formula were all working fine, and data was being pulled across into the datasheet fine. But suddenly every single formula is now saying "#invalidref".
As far as I can see, the structure of the source sheet has not been changed (no new columns, no column re-ordering).
I have re-entered the first few formula and they work again. But I don't want to re-enter all 100 formula if it is going to break again.
The problem surely lies in the source sheet, or does it?
Any help is appreciated.
Answers
-
Hi Tom,
Try changing the name of the range(s) to force a refresh.
Did it work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Andrée.
Change name of ranges where?
-
Happy to help!
If you click on one of the cells in the destination sheet, you'll see a link with the reference name below. Click on that and change the name at the top of the window.
Did you find it? Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Most of the formulas in the destination sheet have three arguments. Each argument references a different column in the source sheet. I understand the "range" to be the column in the source sheet.
When the error #invalidref appeared in the destination sheet, when I click into the formula only two ranges are referenced. One of the three ranges has disappeared in every formula (but not the same range each time).
Which suggests that something in the source sheet must have changed.
What changes to the source sheet will affect formula? A column being deleted obviously. Anything else?
-
Usually, the Invalid ref is because the name in range in the formula is wrong.
Yes, if the range (column in your case) is deleted.
I'd be happy to take a look if you want?
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives