How t check "Trace Dependents" ?

khardu

Board Regular
Joined
Jan 18, 2012
Messages
68
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
If you are familiar with VBA, then you might be able to develop some code using the following.

Code:
Range.ShowDependents

Range.Dependents
 

khardu

Board Regular
Joined
Jan 18, 2012
Messages
68
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

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
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.

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,221
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top