+ Reply to Thread
Results 1 to 10 of 10

Tracing ALL cells that a cell is a function of

  1. #1
    Registered User
    Join Date
    04-13-2019
    Location
    England
    MS-Off Ver
    Excel 2016 MSO
    Posts
    6

    Tracing ALL cells that a cell is a function of

    Apologies if this is answered

    Hi all, I want to find a way to get a list with ALL the cells which a cell value (D1) depends on. I know about the Trace Precedents tool, but it will only show cells which are in cell D1's formula, and I want to avoid doing lots of Trace Precedents for each previous cell.

    So if I have a cell D1, which depends on C1, which depends on B1, which depends on A1. But if I use trace precedents, it will only show me C1. However if A1 or B1 change, D1 will also change. Is there a quick way to display a list with all the cells that D1 is a function of?

    Thanks.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Tracing ALL cells that a cell is a function of

    Have you tried the other way (trace dependents)? It points to all instances.
    Last edited by AliGW; 04-13-2019 at 07:28 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-13-2019
    Location
    England
    MS-Off Ver
    Excel 2016 MSO
    Posts
    6

    Re: Tracing ALL cells that a cell is a function of

    Hi Ali, thanks for the fast reply, but Trace Dependents doesn't help me. I need a way to display ALL THE CELLS that act as input for a target cell (say D1), even if they do so indirectly and are not necessarily just the cells that are in D1's formula. So say:

    D1 = B2 +4
    B2 = A1+ 6
    A1 = 5

    And if I use Trace Precedents on D1, it will only point to B2, and I would then need to do a Trace Precedents on B2 as well, to get to A1. The cells might be many and on different workbooks and in my case it would take a long time to track them like that. So I wonder if there is a way to get a list of all cells at once.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Tracing ALL cells that a cell is a function of

    I don't know of a way natively. Shall I move your thread to the VBA section?

  5. #5
    Registered User
    Join Date
    04-13-2019
    Location
    England
    MS-Off Ver
    Excel 2016 MSO
    Posts
    6

    Re: Tracing ALL cells that a cell is a function of

    Yes, thank you Ali

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Tracing ALL cells that a cell is a function of

    Moved to VBA section.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Tracing ALL cells that a cell is a function of

    Hi,

    The Inquire Add-In has, amongst other useful features, a Cell Relationship command, which may be of use to you.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Tracing ALL cells that a cell is a function of

    Inquire is not available in all editions. From the Office Support page:

    Inquire is only available in the Office Professional Plus and Office 365 Professional Plus editions.

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Tracing ALL cells that a cell is a function of

    Quote Originally Posted by AliGW View Post
    Inquire is not available in all editions. From the Office Support page:
    Good spot, thanks.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Tracing ALL cells that a cell is a function of

    No problem - I was excited and went looking for it, but of course, couldn't find it. Never mind!

+ 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. Lineage tracing - VBA newbie help
    By Kevin_M in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2013, 10:14 PM
  2. [SOLVED] error tracing #REF!
    By mikebon111 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2013, 08:16 PM
  3. Excel 2007 : #VALUE - Tracing problem cells?
    By antispam246 in forum Excel General
    Replies: 12
    Last Post: 11-27-2009, 11:51 AM
  4. Tracing dependents
    By john9210 in forum Excel General
    Replies: 2
    Last Post: 03-09-2009, 08:19 AM
  5. Tracing the contents of a cell to a sheet
    By EMGUY in forum Excel General
    Replies: 3
    Last Post: 04-11-2008, 10:09 AM
  6. Tracing links
    By olechren in forum Excel General
    Replies: 1
    Last Post: 02-15-2008, 09:46 AM
  7. Tracing contributors to sum total
    By prod in forum Excel General
    Replies: 4
    Last Post: 12-06-2007, 10:18 AM
  8. Tracing Links
    By Stephen Pain in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 09:41 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