BUG in Excel re formula precedents

jasmith4

Active Member
Joined
May 16, 2008
Messages
337
Hi, I thought I’d share this to get your thoughts. I have only 2010 – does anyone get the same with 2007, 2013, 2016, etc.?

Steps to reproduce:

1. Create a new workbook with at least two tabs.

2. On one tab make a table (ListObject) with headers and data
a. In cells A1:A2 enter a label (for the header) and a number (for the data point)
b. Select A1:A2, hit Ctrl-T to make a table, and click OK.

3. On another tab (cell A1) begin entering a formula by typing an equal sign (=).

4. In formula-entry mode switch to the tab with the small table, click cell A2, and hit Enter to finish the formula.
It will look like "=Table1[HeaderLabel]".

5. With the formula still selected click the Trace Precednts button on the Formulas ribbon.
BUG: it says there are none!

6. Go back to the small table, right-click it, and choose Table / Convert to Range.

7. Go back to the formula cell, and click Trace Precedents.
This time it works: you get a tracer arrow to the other tab.

8. Hit Ctrl-Z to undo converting the table to a range.

9. Go to the formula cell, where you'll still see the tracer arrow, and double-click the arrow.
BUG: the dialog box of links is empty!

This doesn't happen if the table and the formula are on the same tab.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I can duplicate this behavior in XL 2016.

I'm pretty sure it's related to the fact that it's just a 1 row table (header row isn't counted)
By definition, a table is multiple rows of data.
The issue doesn't happen if there are at least 2 rows of data (not counting the header) in the table.

So when you create your link, it's a link to the entire column of the table.
Why that causes this issue, I have no idea.
 

jasmith4

Active Member
Joined
May 16, 2008
Messages
337
No, I found it in "real life" -- that is, I have a formula that does a SUMIF on a very large table: it looks down one column for IDs that match a given one and adds up another column of balances to get the given ID's total balance.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Interesting, also duplicated in XL2016.

Should note that the opposite is also true, Trace Dependants from the Table finds no formulas referring to it.
 

jasmith4

Active Member
Joined
May 16, 2008
Messages
337
We at S&P have a contract with Microsoft, and I have some contacts there, so I've informed them about this. They says it's been present and known since Excel 2007, but without "sufficient business impact" they're not going to do anything.

Well, gee, at S&P we develop serious financial models that analysts use to determine ratings for deals and companies that we publish as our line of work. These models undergo exhaustive auditing, version control, build/QA/deploy, the whole bit, believe me. And formula auditing is a pretty important part of all that, so I would say it's "serious business impact".

Come on, guys, the fact is, it's a bug. I hope some MVP is listening -- I'll send anyone a simple test-case file who asks for it.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
We at S&P have a contract with Microsoft, and I have some contacts there, so I've informed them about this. They says it's been present and known since Excel 2007, but without "sufficient business impact" they're not going to do anything. [....] Come on, guys, the fact is, it's a bug. I hope some MVP is listening
The MVPs in this forum are not Microsoft MVPs, AFAIK. Most Microsoft MVPs participate in the microsoft.com forums. They have their own private forum; I don't think we plebes have access to it. But you might reach them through social.technet.microsoft.com. Also answers.microsoft.com.

That said, I would not hold my breath for results. First, even Microsoft MVPs have little influence over Microsoft. Second, historically, Microsoft has a poor record of fixing problems, even those reported by OEMs (moi! once). Finally, "sufficient business impact" refers to Microsoft, not the customer. How many $$s of revenue does S&P represent to Microsoft? (Rhetorical, not provocative.)

But appealing to Microsoft MVPs is not a total waste of time. They have gone to bat for me once or twice, albeit not with any positive results. (The fault is with Microsoft, not the MVPs.)

Good luck!

PS.... Oh, I see you already have a thread at answers.microsoft.com. Again, you might get more traction by posting to social.technet.microsoft.com. And again, don't have high expectations.
 
Last edited:

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top