Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How 2 know which cells reference a particular cell

    (Excel 2000/SR-1) I have a large spreadsheet, many cells in which contain formulae which reference other cells. I want to move one particular item from cell C3 to somewhere else; I won't be adding or deleting any rows or columns, just moving C3's contents. Obviously, all formulae that reference C3 will become invalid. Is there a way for me to determine which cells in my spreadsheet are referencing C3 so I can change the value from C3 to (whatever)? I would prefer this done without a macro, if possible, otherwise I will accept a macro solution.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Hi stumped,

    If you select C3, and go to Tools, Auditing and select Trace Dependants, and arrows should then be drawn from C3 to all cells that reference C3 in their formulae. If you want to find the cells the C3 is dependant on choose Trace Precedents.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Another way:

    Select cell C3. Now, select GoTo from the edit menu. Click on the "Special" button. Select Dependants and also select either Direct or All levels. Click OK, and all of the cells with formula that depend on C3 should be selected.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Thanks for those quick answers - both work great. And to think it was so simple!!!

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Legare,
    What about dependents/precedents to C3 that are in another worksheet(s)? How might I find out what cells in other worksheets in the same file are either dependent upon or precedent to, say, C3?
    Thanks,
    Jeff

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How 2 know which cells reference a particular cell

    If a cell has precedents in another worksheet, "Trace Precedents" will display an arrow an a small worksheet icon. If you point to the arrow, the mouse pointer will change to a northwest arrow. Double clicking will call up the Go To dialog. It's similar if the cell has dependents in other worksheets.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Thanks, Hans.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular cell

    Everyone answered the question that you asked, but I think one comment that you made should be addressed also. You said that you want to move C3. If you Cut C3 and paste it somewhere else, then all of your formulas should automatically be updated. You wouldn't have to edit everything.

    Also, if you have C3 named and use the name in the fomulae instead of C3, then you can redefine the name with the new location.

    Either of these would work without having to edit all of your formulas. Still it's good idea to know what affects what.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How 2 know which cells reference a particular

    Stumped,
    Using the Audit tools will help you investigate how a particular cell is related to the rest of the workbook. If all you want to do is just to move C3's contents somewhere else, Cathy's suggestion to merely Cut and Paste works even as far as pasting into another open workbook - carrying the links to precedents and dependents with it.

    A quick method to find Precedents only is to select C3, then press F2 - Edit. The immediate precedent cells are color-code linked to their references in the edited cell.

    Errol

Posting Permissions

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