can't see what's causing the #invalid ref in my cell
Hello, the below formulae exist in my sheet. The first one works whilst the second returns a #invalidref error. The snapshot shows that all references exist and are active in the sheet. The second one shows the correct cross reference for the difference in which the calculations are fine. Even more confusing is the broken formula works in the original source sheet (the one for ALL locations). Please help me find what's broken, thanks!
works fine: =IF(COUNTIFS({HospState}, "", {Dept}, Departments@row, {HospAffil}, "yes", {loc}, "philadelphia") = 0, "", COUNTIFS({HospState}, "", {Dept}, Departments@row, {HospAffil}, "yes", {loc}, "philadelphia"))
doesn't work: =IF(COUNTIFS({HospCity}, "", {Dept}, Departments@row, {HospAffil}, "yes", {loc}, "philadelphia") = 0, "", COUNTIFS({HospCity}, "", {Dept}, Departments@row, {HospAffil}, "yes", {loc}, "philadelphia"))
Answers
-
Curiouser and curiouser! I made a copy of the sheet and the formula worked right away. I make updates to reflect a new location, save, and return to the original 'broken' sheet, and watch as the sheet runs through #calculating and then returns the counts for me with no errors! So new question: why do formulae randomly break and then start working again? :thinking-face:
-
In October my VLOOKUP and MATCH formulae broke, but only on a handful of sheets, and only in certain columns on a couple of critical sheets. I talked through it with a couple of pro support staff and they were just as stumped. We came up with a work around, but there was no real answer as to why it was some and not all VLOOKUP formulae, especially when I had the exact same formula doing the exact same thing, referencing the exact same source data on a different sheet and it was in working order. Thankfully, it's not happened on that scale again. Copying didn't fix my problem, but there does typically seem to be a way around it. Pro-support is helpful, if you have access to that resource.
-
Thanks, Austin!
-
Hello, I'm experiencing something similar: My formula breaks, and one of my sheet references disappears. I get errors: "BROKEN" and "INVALID REF" in a few columns. But them after I wait a bit, or refresh my sheet, it all loads back in perfect working order. Does anyone know why this could be happening? Thanks in advance!
-
Hi @JGIL83
I hope you're well and safe!
Are you still having issues?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!