"Excel found a problem with one or more formula references" - Please tell me where!
One of my least favorite messages that Excel provides is:
"Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct."
But I can never find the bad references. It's usually buried somewhere very deep, in a chart series formula, or in a name definition, or who knows where else.
If Excel finds a problem, please Excel, tell us where it is.
Jon Peltier
shared this idea
34 comments
-
F. Gress
commented
@Roy, so glad to have you commenting here.
I see you put a heroic amount of time into this community; keep up the good fight!So many of the comments in this thread are hilarious, in a 'misery loves company' kind of way.
-
Roy
commented
@Dave Horton:
This is not that kind of thing. This is a message that tells you there is some problem somewhere but not where that where actually is.
I went into depth on it, 8,344 characters, can't post... and I chose not to try trimming 40% of that so short answer:
The objection is that, IF Excel can see a problem exists, wherever that is, whatever it just tried, it OUGHT to be able to tell us exactly what items were involved in whatever just failed.
See? It's not about what we can eventually figure out for ourselves or not, or the long hours of work to do so, but rather that, if it was checking or trying to use something, it ought to know exactly what that something was and ought to tell us what it was. And let us rip.
But it doesn't. It just says "Oh man, like, you know, there's like this problem man and like, I can't, you know, just can't and oh man, like, you gotta fix it man, you gotta fix it." "Where? What was it?" "Like man, you HAVE TO FIX IT MAN!" "Yeah, yeah, I will, but where, what?" "You gotta fix it man!"... and stuck on stupid. No help.
THAT's the complaint here. That it ought to know and therefore tell us, but either it doesn't so it can't, or it does and rather obtusely, it just won't." Not whether you can get lucky in searching for it and solve it fast, or take 35 hours of searching, but that it should just tell us where and what.
You know man, like so it can, um, be fixed much, much more easily man.
-
David Horton
commented
365 has in the Formulas Tab a Formula Auditing Area
-
Anonymous
commented
"It's amazing that users have been complaining about this most exasperating Excel error message for years, and Microsoft just ignores them."
M-Soft couldn't care less about our input, as long as they keep making money.
Be fair, they are a struggling company, and need the cash (cough).
Complain here all you like - they couldn't give a flying ****.
-
Anonymous
commented
It's amazing that users have been complaining about this most exasperating Excel error message for years, and Microsoft just ignores them.
-
zainab ahmed
commented
I can not open sheet of excel to work in it.
-
Marco Zamora
commented
Infuriating, useless error message.
-
Nechifor Corneliu
commented
I prepared a table of excell OpenOfice on my PC-Corneliu
-
James Goodhew
commented
Meantime, I found this link very helpful, though as it's you Jon who posted it, I know you already know! Posted purely for others struggling with this:
https://www.damirscorner.com/blog/posts/20160918-FindInvalidLinksInExcelThatCannotBeBroken.htmlMicrosoft please do tell us exactly where the bad references are in our workbooks.
-
Roy
commented
I always wondered if you were to have a closed spreadsheet that had a bad formula in it which the one you were opening referred to sometimes triggered this. You know, just 'cause an extra layer of h*ll to worry about made it grimmer I guess.
Actually, still wonder if that can do it.
If they can spot a bad link or formula, and give an error message concerning it, they can tell us what sourced it. Given their "let the program ***** up even the simplest thing it insists upon doing 'for you' whether you like it or not" successes, I cannot accept that it could get unspecified errors and realize they must be this and so can't, as I posit, tell us what caused them.
Can't get "Let me open that CSV file for you" right means it absolutely is NOT doing the above. So it should be easy to let it tell us what and where.
-
Nice Brian
commented
I used to get this a lot. For me it was always in conditional formatting, someone's copied cells from another workbook that were conditionally formatted with a formula, and that formula linked to the workbook they copied the cells from.
Now I just don't ever use conditional formatting, and do all my colouring-in using VBA macros. Voila, the problem slowly but surely went away.
I agree though, I spent days if not weeks tracking down these "broken links" which Excel wanted to warn me about EVERY. SINGLE. TIME. I opened one of the affected workbooks, but kept their actual location a secret!
-
CLAUDE VAN HORN
commented
To find errors on a sheet, just open the formulas tab and click "error checking"
-
Anonymous
commented
Can not import PDF to Excel
-
Anders Ebro (TheSmileyCoder)
commented
It seems clear to me, that the only focus for MS now is the cloud versions of excel. Several desktop related issues are not being adressed. VBE has received no love for 15 years, and they don't want to invest in that either, despite millions of users depending on it every day.
-
Anonymous
commented
Agreed. Amazes me how they manage to make the look and feel of Excel worse with each new release, change for the sake of it, yet don't fix problems that real users in the real world want fixing, problems they've known about for many many years.
-
Mitch
commented
GLOBAL F9 for the Masses
Well, if you're going to fix this, go all in and give us "Stack Trace", so we can follow the logic Excel uses to arrive at the problem or, in general, the solution.
-
Johnson
commented
"Hi! There's supposedly a problem, but I can't be bothered to tell you what it is. Instead I'm just going to beep at you and give you a pop-up to close - every time you do anything, from now until the end of time."
Gee, I can't imagine how anyone could find that annoying. XD
-
Adaxis
commented
Please
-
Ingeborg Hawighorst
commented
Yes! If Excel can determine that there is a problem, it shouldn't be that hard to tell us where.
-
Enerkey Roger
commented
I am using windows 10. I also works on excel for my job.somedays ago I faced with this error the I saw this sitehttps://msofficetechnicalsupportnumbers.com/blog/how-to-fix-ms-excel-has-stopped-working-error/
and get a solution. You have to check something such as isolate the specific spreadsheet tab, check external links, check the name manager, check your charts, check your pivot tables. After you can solve the problem
