BUG in Excel re formula precedents

[jasmith4]

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]

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]

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]

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]

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]

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.