Consolidating data from 40 worksheets

**rheller** · 06-06-2005, 02:22 PM

I have a bunch of worksheet templates (about 40) sent out for people to fill out. I am looking to consolidate the data in one master excel worksheet. What is the best way to accomplish this? I have tried linking sheets, but within the master sheet worksheet, I am using =SUM() and the formula just gets too long. Any suggestions would be greatly appreciated. Thank you.

**rheller** · 06-06-2005, 03:56 PM

I decided to use the DATA->CONSOLIDATE on the menu. This seems to work fine as I am able to add from 26 worksheets into a single cell. Thanks.

Andy Wiggins · 06-06-2005, 05:05 PM

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's accounts.
(No VBA used)

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"rheller" <[email protected]> wrote in
message news:[email protected]...
>
> I have a bunch of worksheet templates (about 40) sent out for people to
> fill out. I am looking to consolidate the data in one master excel
> worksheet. What is the best way to accomplish this? I have tried
> linking sheets, but within the master sheet worksheet, I am using
> =SUM() and the formula just gets too long. Any suggestions would be
> greatly appreciated. Thank you.
>
>
> --
> rheller
> ------------------------------------------------------------------------
> rheller's Profile:
http://www.excelforum.com/member.php...o&userid=24081
> View this thread: http://www.excelforum.com/showthread...hreadid=376869
>

**rheller** · 06-07-2005, 08:58 AM

Thank you for your help. Is there a way to consolidate the data so that it shows a name (perhaps off a left menu) that will show me quickly which data it is sourcing the information from when the consolidation is expanded by clicking on that arrow?

Is it possible to copy a consolidation function I used by going to DATA->CONSOLIDATE to other cells? You would figure that when I copy the cell in the master sheet to the cell just to the right of it, that it would duplicate the process, but it doesn't seem to.

Andy Wiggins · 06-07-2005, 12:05 PM

Whatever is betwen the "Bread" and "Roll" sheets is within the
consolidation.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"rheller" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you for your help. Is there a way to consolidate the data so that
> it shows a name (perhaps off a left menu) that will show me quickly
> which data it is sourcing the information from when the consolidation
> is expanded by clicking on that arrow?
>
>
>
> Is it possible to copy a consolidation function I used by going to
> DATA->CONSOLIDATE to other cells? You would figure that when I copy the
> cell in the master sheet to the cell just to the right of it, that it
> would duplicate the process, but it doesn't seem to.
>
>
> --
> rheller
> ------------------------------------------------------------------------
> rheller's Profile:
http://www.excelforum.com/member.php...o&userid=24081
> View this thread: http://www.excelforum.com/showthread...hreadid=376869
>