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

Estelle Redding
Estelle Redding ✭✭✭✭
edited 03/22/23 in Formulas and Functions

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

  • Estelle Redding
    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
    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
    Estelle Redding ✭✭✭✭

    Thanks, Austin!

  • JGIL83
    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å
    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!

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!