Solved summarizing a variable number of worksheets in Excel

December 23, 2013 at 11:58:37
Specs: Windows 7
I have created a project workbook "template". The heart of the workbook is a "scorecard" worksheet. Each project will have a variable number of these "scorecard" sheets (Project A might have 3 scorecards, Project B might have 7 - Note, these are multiple copies of the same scorecard, data will be different for the detailed area of the project).

I want to create a summary sheet in the workbook.

A 3D formula can get me the average score *across* the variable number of scorecard sheets, but I'd also like to enumerate some of the details from each scorecard (e.g., the name and sub-score from each scorecard). How can I reference a variable number of worksheets on that summary sheet?

Scorecard 1 Name Score
Scorecard 2 Name Score
Scorecard n Name Score

NOTE: Name and Score are always in the same cell for all copies of the scorecard sheets.

See More: summarizing a variable number of worksheets in Excel

Report •

December 24, 2013 at 14:57:14
✔ Best Answer
Start with the technique found here to create a list of Sheet names:

As mentioned at that site, you can drag the formula down as far as you could possibly ever need, using IF(ISERROR(formula from that site), etc.) to hide any errors if you drag down farther than you need.

Once you have the list sheet names in e.g. Column A, use the INDIRECT function to reference the "same cell " in each sheet. Again, use the ISERROR function to hide any #REF errors.

Let me know if you need any more details.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 3, 2014 at 18:30:24
Thanks for the info. Works great!
(I had a little trouble getting the name defined til I figured out that protection was preventing me from getting to the Define Name command)

Thanks agin!

Report •

Related Solutions

Ask Question