Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Locating cells that are used in formulas (XL 2003)

    Once weekly, I paste a spreadsheet (received from another source) into a workbook that I've created. It is pasted on to a sheet named "Source" This particular sheet does not contain any formulas, links, etc. It's just values and text.

    In my workbook, there are six other tabs (sheets) that pull information (values and text) from the Source sheet. This is how I generate many useful reports. Each week when I get a new spreadsheet from the company, i paste it onto the Source sheet and all my other sheets change or update accordingly.

    Now here comes the question... Now I have a need to recall exactly which cells on the Source page are actually used by the other sheets. The spreadsheet (source) is 10C X 380R. Not every cell or value is used in my other reports. Somehow, some way, I'd like to be able to see which cells are referenced in other formulas throughout my workbook. In the end, I would add color (shade) those target cells.

    Any ideas or thoughts (other than I should have been more organized in my approach when I first set up the workbook !!!) ? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    - Ricky

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locating cells that are used in formulas (XL 2003)

    I use the following code to cycle through every cell in a selection and display the precedent arrows. If you run this on your 'Source' worksheet it should do what you are looking for.

    Sub RangeDependentsShow()
    Dim rngCell1 As Range
    Application.ScreenUpdating = False
    For Each rngCell1 In Selection
    rngCell1.ShowDependents
    Next rngCell1
    Beep
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locating cells that are used in formulas (XL 2

    Thanks Roger, I appreciate the code. Now I have the "useful" cells shaded differently.

    Once in a while, my reports would have some obscure number on it and it was a hassle tracing that back to the source of the problem. Now I can simply look at the Source sheet and take a quicker glance at the shaded cells for an obvious problem...

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

Posting Permissions

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