"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.
Edward Paul commented
I too have encountred the same Excel error meanwhile using formula in my worksheet. so i can tell you how i have overcomed this annoying Excel found a problem with one or more formula references error.
Here is the link of the post from which i got those helpful fixes.
A pet hate, and while your looking for the problem M$ thinks you need to see the dialogue again and again and again and again and again and again and again ..... How is this helpful M$??!!
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.
David Horton commented
365 has in the Formulas Tab a Formula Auditing Area
"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 ****.
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:
Microsoft please do tell us exactly where the bad references are in our workbooks.
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"
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.
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.
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.
"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
Ingeborg Hawighorst commented
Yes! If Excel can determine that there is a problem, it shouldn't be that hard to tell us where.