can't see what's causing the #invalid ref in my cell

Estelle Redding

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"))

Estelle Redding

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:

Austin Smith

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.

Estelle Redding

Thanks, Austin!

JGIL83

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!

Andrée Starå

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!