How to prepare summary sheet in the same workbook have multiple sheets?

purav82

New member
Joined
Jun 24, 2013
Messages
6
Reaction score
0
Points
0
Hi everybody,

I am new to this forum & having a problem in preparing a report. My query is as under:

I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that I want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report I will have to give reference for around 6000 times.

I would be grateful if any one of you could show me a way out. Just to let u know I have no idea about VBA.
 
I don't think a Pivot will be any help here - unless you use SQL to populate it with data - because data is spread over several sheets.
What version of Excel do you have?

This question is very similar to one I answered recently over at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables so you might want to check that thread out. Basically, here's three methods to do what you require:

Method one:
The below code refreshes a table caled 'Summary' from tables in seperate sheets. Those tables are called 'DDA_Priorities', ' Footpaths', and 'Other'.

Basically it wipes the Summary table clean any time the Summary sheet tab is activated, then copies the info from the other tabs into the blank Summary table, and sorts them.This could easily be adjusted to suit your needs. Happy to help you do that, if you can post a sample workbook.



Private Sub Worksheet_Activate()

Dim lo As ListObject
Dim lr As ListRow
Dim rngSource As Range
Dim rngDest As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set lo = [Summary].ListObject
With lo

' Clear the Summary table without actually deleting it
On Error Resume Next
.DataBodyRange.Rows.Delete
On Error GoTo 0

'Copy the first source table into the Summary table.
Set rngSource = [DDA_Priorities].ListObject.DataBodyRange
Set rngDest = .HeaderRowRange.Offset(1)
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

'Copy the other tables in. Note that we can just add a
' listrow, and then copy the data there.
' But we couldn't do that above, because there weren't any
' listrows to add another one to, which is why I used the
' Set rngDest = .HeaderRowRange.Offset(1) line.

Set rngSource = [Footpaths].ListObject.DataBodyRange
Set rngDest = .ListRows.Add.Range
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

Set rngSource = [Other].ListObject.DataBodyRange
Set rngDest = .ListRows.Add.Range
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

With .Sort
.SortFields.Clear
.SortFields. _
Add Key:=Range("Summary[[#All],[Priority]]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End SubI've uploaded a sample file to https://www.dropbox.com/s/ejv28zpgxk6slf0/Amalgamate Tables to Summary Table 20130622.xlsb

Method two: mash this data up is with a little SQL. Then you can serve it up in a pivottable. See http://blog.contextures.com/archives/2010/09/01/combine-data-from-two-excel-files-in-pivot-table/

Method three: Use PowerPivot, which is a free add-in for Excel 2010 (and unfortunately only availble in Excel 2013 professional plus edition due to some strange marketing from Microsoft. What version of Excel do you have? If 2010, then you can download and install PowerPivot for free. And there's a tutorial at http://www.contextures.com/PowerPivot-Identical-Excel-Files.html
 
Thanks Roy for replying quickly. But I think I'll first try what Jeffery has written.
 
Thanks Jeffrey. I am using Excel 2010. Also I have attached a sample excel workbook for your reference. Hope that it might help. The workbook contains 6 Sheets. The first sheet is the report that I want & the remaining sheets are the source of data from which the report is to be prepared. As u can see the Report sheet is nothing but it contains all the data of other sheets in horizontal format.

Have a look at it & let me know which method should I go for. Just to add further, the data in the Report sheet should get updated automatically whenever any changes are made in the other sheets.
 

Attachments

  • Sample Sheet Employee wise details.xlsx
    37.6 KB · Views: 58
Ah. That's not quite so straightforward. None of the above methods will work given your data isn't in a table (i.e. with column headers across the top and data below each header). But that's okay, I can still find an easy approach for you. I also note that tab C doesn't have 'Leave Salary' while the others do. Is this an oversight, or is it possible that the structure of the data might not be identical from tab to tab given an employee's circumstances?

Also, how many employees are there likely to be? Tens, hundreds, or more?
 
Last edited:
Okay, here's two approaches. View attachment Sample Sheet Employee wise details.xlsx

Both rely on you setting up LOCAL named range called 'Data' for each sheet (and I've already done this, but I've put the instructions down below if you want to know how). In fact, you don't really need to do it again, because if you make a copy of an existing sheet, then Excel will add another LOCAL named range called 'Data' for that sheet.

You enter a LOCAL named range via the Name Manager. You click on NEW, give the new name a name (I just called these 'Data') and then change the Scope dropdown from Workbook to the particular sheet you are setting the name up with, and then enter the range it refers to in the 'Refers to' field. Note that changing that scope dropdown to a particular sheet is what makes it a LOCAL name (sorry to shout) and is very important to my implemetation. Google Local vs Workbook named ranges, and have a read.

So here's the two formulas:

The first is simplest, but uses the volatile INDIRECT function. The formula entered in cell C3 is =VLOOKUP(C$2,INDIRECT("'"&$B3&"'!Data"),3,0). This formula is then dragged down and across to cover the entire range of your summary table.
If you want to add a new employee, you just copy an existing employee's sheet (which will create a new local name called Data in the process) and then overwrite the details with the new information, and then put the employee's name in the 'Employee Name' column at the bottom of your summary table and copy the formula above down another row. Simple.

The 2nd uses a non-volatile CHOOSE function, but is much more of a pain to maintain, because in addition to the above steps you also need to add a new range in the function argument list any time you add a new staff member.

That is, if you were adding a new staff member called F, then you would need to change this:
=VLOOKUP(C$2,CHOOSE(MATCH($B12,$B$12:$B$16,0),A!Data,B!Data,'C'!Data,D!Data,E!Data),3,0)
...to this:
=VLOOKUP(C$2,CHOOSE(MATCH($B12,$B$12:$B$16,0),A!Data,B!Data,'C'!Data,D!Data,E!Data,F!Data),3,0)
...and then copy that formula down and across the worksheet.

So what is this Volatile vs Non-Volatile stuff about? Glad you asked. Check out http://chandoo.org/forums/topic/what-is-indirect-function#post-115342 for some references on the INDIRECT function as well as a commentary from me on what the down side of Volatile functions is. In fact, in this specific case, this is one of the few times I would consider using INDIRECT.
 
Wow Jeffrey. You have really done some hard work answering my question. Can't Thanks enough. Well, both the formula's INDIRECT & CHOOSE are new for me, but I'll try to follow the steps you have written & also read the article in the link provided.

Just to let you know there would be around 200 employees like this. So that makes 200 sheets in a single workbook.
Give me some time to so that I can experiment with your solution & will surely let you know whether it worked for me or not.

Thanks again Jeffrey!!!
 
No problem, Purav82. I'm using questions such as yours to develop a training program and associated resources (hopefully including a book), and looking at real world problems is far easier than trying to come up with made up ones.

There's another post with lots of stuff regarding INDIRECT, INDEX, MATCH, and CHOOSE at http://www.excelguru.ca/forums/show...splay-a-list-if-conditions-are-met-excel-2010 that you should check out, if you want to see some more applications of these concepts.

And if you want to see some real mindbogglingly crazy formulas in action, check out these formula challenges:
http://chandoo.org/forums/topic/return-everything-in-string-after-first-block-of-numbers
http://chandoo.org/forums/topic/formula-challenge-002-calculate-conditional-cumulative-sum
http://chandoo.org/forums/topic/formula-challenge-003-count-unique-characters-common-to-two-strings

By the way, if you want to learn how to do this stuff, then I can suggest Chandoo's Excel School and Daniel Ferry's Excel Hero Academy as worth their weight in gold. Ken Puls - who started this forum - probably has a great training programme also.
 
Last edited:
Back
Top