Locating Source Data in Master Spreadsheet

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I routinely use a spreadsheet that has one master spreadsheet that sums data
from about 30 "child" spreadsheets in the same workbook. Not all of the
"child" spreadsheets contain data in various cells and will have a "0" if
there is no data to sum. What I would like to be able to do is generate a
list of the "child" spreadsheets that add up to the number in the master. If
there's an error in any of the "child" sheets, it can be very tedious to
scroll through each spreadsheet to see which one is causing the totals in my
master to be off. I'm wondering what is the best way to do this.

Thanks!
 
And to ease the pain of listing this:
List all the child sheetnames in B1 across, eg: Sheet1, Sheet2,...
Click to expand...

you could run this sub in a new sheet to list all sheetnames at one go in A1
down, then just do a copy > paste special > transpose into the master's B1
across

Sub ListSheetNames()
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
 
To unravel/decompose a 3D summation is tough. One thought is to try this easy
alternative using INDIRECT which enables you to quickly create a summary
"panel" to gather what's in each child sheet's designated cell. Childsheets
are assumed identically structured.

In your master sheet,
List all the child sheetnames in B1 across, eg: Sheet1, Sheet2,...
List all the designated cell refs (that you're summing) in A2 down, eg: B2,
E2, etc
Then place this in B2:
=IF(ISBLANK(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIRECT("'"&B$1&"'!"&$A2))
Copy B2 across and fill down to populate. To the right of the populated
table, you could just use a simple SUM in an adjacent col to gather the
totals. There, you have the one-glance bird's eye view of all of the
childsheets' target contents in a neat panel, which should serve your
objective(s). Inspiring? hit the YES below
 
Yes, seeing all the data on one page by child sheet without having to enter
them all manually would definitely help. I ran your macro and did a small
scale replica of my spreadsheet. I think this will work. Thanks!
 
Back
Top