Solved

Consolidating in Excel

Posted on 2014-03-27
6
134 Views
Last Modified: 2014-03-27
Hi,

I have 20 worksheets in EXACTLY the same format in one workbook.

There are 200 numeric fields in each sheet.

I want one SINGLE consolidated sheet with each field "summed".

What is the easiest way to consolidate?

I am aware that Excel has a consolidation function but that seems to force me to repeat keying tasks multiple times.  i.e. for each of the 200 numberic fields I would need to repeat the same actions 20 times (i.e. one for each sheet)
0
Comment
Question by:Patrick O'Dea
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39959521
Copy one of your sheets to a new sheet, that you either place before or after your other sheets (not in the middle)

Select all your numeric fields (maybe they're grouped, maybe you'll have to use F5, Special, Constants, Numbers or something like that to select them),

Type your consolidating formula, from the first sheet to the last sheets, e.g. if they're named sheet1 to sheet20, and you're typing in cell B5

=sum(sheet1!B5:sheet20!B5)

validate with Ctrl+Enter to fill all cells

You're done.

Thomas
0
 

Author Comment

by:Patrick O'Dea
ID: 39959578
Thanks Thomas for suggestion.

It looks great ...but..

I follow the logic ... one step at time... but I am getting #VAlue!

Perhaps you could have a look at the "consol" sheet. Specifically, what is wrong with cell A1.
EEConsolidate.xlsm
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39959607
Mistake on my part, sorry, the formula should be

=sum(sheet1:sheet20!B5)

or for your sample file
=sum(sheet1:sheet3!A1)

One way to get that is to type =sum(, then select your first cell of your first sheet, then while holding ctrl, select the last sheet.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Patrick O'Dea
ID: 39959702
Ok thanks,
One final question what if the sheet names where not the default values? E.g. Sheet 1= Paris, sheet 2= London etc.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39959727
Same difference, only you'll need single quotes around if you have spaces or other funky characters in the sheet names, as in:

=sum('Paris:Moscow'!A1)

Also, fyi, you should be holding Shift when selecting the sheets, not Ctrl as I said earlier.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39959828
Thanks nutsch,

works perfectly.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now