Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Consolidate Excel Data from multiple sheets into one

I had a problem which others might come across, so I thought I'd post
the answer here in case anyone else comes across the same issue (or
anyone has a better solution).
Here's the original problem:

This one is a "There simply must be an easier way to do this" question
I'm hoping someone has an answer to.

I have an Excel workbook (Excel 2007) which has multiple (30)
worksheets, each of which are in exactly the same format with data for
each individual in a separate sheet (each subject's individual test
sub-scores for things like strength, flexibility, and the like, for
both their left and right sides, and there are about 30 tests/measure
for each subject, each side) that I would like to summarise.
That means there are 60 measures per subject, 30 subjects, so 1,800
data points I'm trying to summarise to 60 averages, and standard
deviations, max and min.
I thought this would be something Excel would have a built-in
function for, and the closest thing seems to be "Data – Consolidate –
Position" but it looks like it involves choosing the function, and
then individually choosing each and every cell in each of the sheets I
want to average.
This is no easier than simply writing the same function in a cell, and
makes me think I am missing something.

After a bit of to and fro on the Whirlpool.net.au Windows forums,
here's the washup:

AdamL had a good idea:
Say you had a value in cell B2 in each of 60 sheets, and you wanted to
average all of these values.
In another sheet, list all the sheet names (say in G1:G60).
Then in B2 of that sheet, Ctrl+Shift+enter:
=AVERAGE(N(INDIRECT("'"&$G$1:$G$60&"!'R"&ROWS(B$1: B2)&"C"&COLUMNS
($A2:B2),0)))

I had forgotten about indirect referencing and concatenating functions
to make up a string, and the Ctrl-Shift-Enter trick. This method
almost certainly will work, but for my purposes, it would be prone to
error as each sheet is named with the individual player's name, so I
would also be adding in space characters which I would first have to
find, and there inevitably is the chance of a typo bringing the whole
thing undone.

In the end, what worked was a combination of the built-in Data-
Consolidate function, and a 3rd party extension to this which makes
the interface a little cleaner.
In my case I used the Office Assistance Advanced Consolidation Manager
as it had a 20 day trial period, but there are plenty on the net, and
I'm sure if I looked for long enough, there would be a free version
somewhere.

If there is a more elegant solution, I'd love to hear it as this will
come up again one day.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default Consolidate Excel Data from multiple sheets into one

Easy enough for Excel 2007 Pivot Table.
No need to enter any formulas or
buy/learn/maintain some add-in.
http://www.mediafire.com/file/xitlzo...04_02_09b.xlsx
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidate data from multiple sheets Bdavis Excel Worksheet Functions 0 September 26th 06 04:25 PM
how do i use import to consolidate data from multiple sheets Naveen Excel Worksheet Functions 1 April 14th 06 12:41 PM
how to consolidate data in different excel sheets Payal Excel Worksheet Functions 1 March 27th 06 03:07 PM
how to consolidate data in different excel sheets paul Excel Worksheet Functions 0 March 27th 06 12:05 AM
how to consolidate data in different excel sheets Franksta Excel Worksheet Functions 0 March 26th 06 11:45 PM


All times are GMT +1. The time now is 12:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"