How t check "Trace Dependents" ?

[khardu]

Hello dear excel lovers.

I've a trial balance and financial statements linked to it. Trial balance consists of a lot of pages. I want to see if there is any value in trial balance which is not feeding the financial statements. Actually there are tons of items and it's hard to check that every item in trial balance has been accounted for.

 

[Al Chara]

If you are familiar with VBA, then you might be able to develop some code using the following.

Range.ShowDependents

Range.Dependents

 

[khardu]

Thank you Al Chara for your reply. I am not familiar with VBA, would you please help me out. I copied and pasted your code in VBA Editore, went to Macro to see if there is any Macro generated by your Code, there wasn't any. I have five sheets linked to a single sheet.


Thanking you.

 

[Al Chara]

In my original post were just some VBA functions for you to check out, by no means was it meant to be a complete solution. Try the following code. It will search for formulas on the active sheet and highlight the precedents for each one. Cells that are not highlighted should not have any decedents.

Sub Precedents()

On Error Resume Next
    For Each mycell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        mycell.Precedents.Interior.Color = 65535
    Next mycell
    
End Sub

Note! It will only work for cells on the active worksheet.

 

[khardu]

Thank you Al Chara, it worked well. You are star!