Results 1 to 2 of 2
2002-11-07, 14:38 #1
- Join Date
- Feb 2001
- Thanked 0 Times in 0 Posts
Tracing Dependents to other Worksheets (XL2000 SR1)
I am trying to develop a utility that can trace dependents and precedents to other sheets in the workbook.
The code that I am using to list the precedents or dependents is :
For n = 1 To ActiveCell.Precedents.Count
This code lists all cells that are contained on the same sheet as the formula, but if the formula includes a cell from another sheet, it does not show up.
Is there away to capture all of the cell address for every cell that impacts or is impacted by another cell?
2002-11-07, 21:14 #2
- Join Date
- May 2002
- Canberra, Australian Capital Territory, Australia
- Thanked 440 Times in 363 Posts
Re: Tracing Dependents to other Worksheets (XL2000 SR1)
You can't do this with the precedents property (which returns an absolute reference even if the underlying formula uses a relative reference). Even
only counts precedents on the same worksheet.
To overcome this, you could parse the formula itself, extracting the references to other cells/ranges/worksheets/workbooks. You might also want to list repeated references only once. Consider too how you want to represent named ranges - 'precedents' returns the range, not its name.
Hope this helps.Cheers,
[MS MVP - Word]