Tracing Precedents

adam2079

Board Regular
Joined
May 15, 2008
Messages
51
Hi everyone,

I'm new to this site so please let me know if I haven't supplied the correct information

I have a standard workbook containing 3 sheets and have the following formula in Cell H7 on Sheet 1

=D11+SUM(Sheet3!A1:A3)+E13+F15+Sheet2!H8+SUM(G13:G15)

What I'm trying to do is to display all the precedents that make up the formula in to cell J9 onwards.

The code I currently have is as follows:

Private Sub CommandButton1_Click()
Dim TheText As String
Dim TheName As String
Dim TheCounter As Integer
Dim iArrowNum As Integer
Dim iLinkNum As Integer
Dim i As Integer

Range("J9:J100").ClearContents
Range("H7").ShowPrecedents
TheCounter = 9
iArrowNum = 1
iLinkNum = 1
TheName = ActiveSheet.Name

Do
On Error Resume Next
Range("H7").NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum _
, LinkNumber:=iLinkNum

If Err.Number > 0 Then Exit Do
If ActiveSheet.Name <> TheName Then
TheText = ActiveSheet.Name & "!" & ActiveCell.Address
Cells(TheCounter, 10) = TheText
TheCounter = TheCounter + 1
End If
Sheets("Sheet1").Activate
iLinkNum = iLinkNum + 1
Loop
Range("J7") = Range("H7").Precedents.Count
If Range("H7").Precedents.Count = 0 Then Exit Sub
For i = 1 To Range("H7").Precedents.Count
Range("H7").Precedents(i).Select
Cells(TheCounter, 10) = ActiveSheet.Name & "!" & ActiveCell.Address
TheCounter = TheCounter + 1
Next i
ActiveSheet.ClearArrows
End Sub

The results I'm trying to obtain in cell J9, J10 etc is:

Sheet3!$A$1
Sheet3!$A$2
Sheet3!$A$3
Sheet2!$H$8
Sheet1!$D$11
Sheet1!$E$13
Sheet1!$F$15
Sheet1!$G$13
Sheet1!$G$14
Sheet1!$G$15

but I'm actually getting

Sheet3!$A$1
<TABLE style="WIDTH: 84pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=112 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 84pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=112 height=14>Sheet2!$H$8
Sheet1!$D$11
Sheet1!$D$12
Sheet1!$D$13
Sheet1!$D$14
Sheet1!$D$15
Sheet1!$D$16


</TD></TR></TBODY></TABLE>

I've tried many different ways of doing this for the past few hours and have drawn a blank.

Can anyone help me?

Many Thx
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

adam2079

Board Regular
Joined
May 15, 2008
Messages
51
Hi again,

I've just realised that my problem is more advanced than i first thought. Essentially what I'm trying to do is read a huge accounts pack that has many different formulas aside from those listed in my previous post.

The formulas may then lead to other formulas and then to even more formulas before finally picking up a balance from a particular cell or cells.

So as an example if I had a cell with a balance of £100, is there a way of tracing through the spreadsheet to find which cell or cells that balance was picked up from so I could then add a comment to the cell listing those cell references?

I hope that makes sense

Any input would be greatly appreciated
 

adam2079

Board Regular
Joined
May 15, 2008
Messages
51
Hi chaps,

After many hours of work I've finally managed to get the code to do what I want. It ain't pretty but I don't think it was ever going to be
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,448
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top