+ Reply to Thread
Results 1 to 5 of 5

BUG in Excel re formula precedents

  1. #1
    Registered User
    Join Date
    01-14-2011
    Location
    N/A
    MS-Off Ver
    Excel/Access/Word 2010
    Posts
    11

    Exclamation BUG in Excel re formula precedents

    Hi, I thought Id 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.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,228

    Re: BUG in Excel re formula precedents

    I am able to reproduce this in Excel 2013, but only in a table with exactly one row of data.

    If I set this up, then extend the table to more rows, the result of the original reference changes to #VALUE!. This suggests that there is something wrong with how Excel creates the reference to data in a table with one row. That seems to be the root cause of the failure to trace precedents.

    If I run the scenario with a table with more rows, then the reference is a conventional Sheet1!A3 reference.
    Last edited by 6StringJazzer; 04-07-2016 at 10:13 AM.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: BUG in Excel re formula precedents

    2016 (64) - cannot reproduce this "bug" because it works normally.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,228

    Re: BUG in Excel re formula precedents

    sandy666, I was able to reproduce this in 2016 (32-bit Office running on Windows 10 64-bit). Are you sure you followed the steps exactly?

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: BUG in Excel re formula precedents

    Maybe Excel 2k16 32 <> 64 bit (on Win 8.1 64)
    Yes, I did.
    With single row and some more
    With Excel table and Range table

    btw. I've only 2k16 64 bit so I can't try another ver.

    Edit:
    I checked again My fault.
    First time with Table1[Header] I got : (in short) no formula or reference
    Convert to range: Table1[Header] is converted to =Sheet1!$A$2:$A$2
    but if I convert range again to the table, formula =Sheet1!$A$2:$A$2 is not converted to Table1[Header] and working well with Precedents
    something like one-way-ticket
    I read about it somewhere on MS Office site but now I can't find it

    sorry for the mish-mash

    Trace Precedents works by parsing the formula, but has not been properly updated for the flavours of Structured References that arrived in XL 2007 and 2010 or higher.
    To solve the problem is to write own formula parser that understands both 2007, 2010 or higher, structured references.
    or
    Formula Tokenizer
    Last edited by sandy666; 04-07-2016 at 03:08 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. WHy so hard to get a cell's formula dependents and precedents??
    By jasmith4 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2014, 03:30 PM
  2. Replies: 0
    Last Post: 05-31-2012, 09:58 AM
  3. Excel go to precedents/dependents hotkey.
    By quekbc in forum Excel General
    Replies: 0
    Last Post: 06-23-2011, 05:49 AM
  4. [SOLVED] WHAT ARE REASONS THAT A FORMULA WILL NOT RESPOND TO PRECEDENTS?
    By Bizman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2006, 03:00 PM
  5. [SOLVED] Excel XP: Showing Trace Precedents Arrows for Multiple Cells...
    By Birmangirl in forum Excel General
    Replies: 4
    Last Post: 09-27-2005, 10:05 AM
  6. Excel Precedents
    By Joshua Bright in forum Excel General
    Replies: 1
    Last Post: 07-28-2005, 02:06 PM
  7. Replies: 3
    Last Post: 01-29-2005, 06:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1