I have created an Excel file with a worksheet for each month's expenses. I
would like to have a final worksheet to sum all the different categories.
How do I add cells from multiple worksheets?
Thanks!!
If the totals on each worksheet are in the same cell you can set up a 3D
reference to them on one sheet.
=SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
on that sheet.
If the cells are randomly placed on each sheet it becomes more difficult.
Gord Dibben MS Excel MVP
On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>I have created an Excel file with a worksheet for each month's expenses. I
>would like to have a final worksheet to sum all the different categories.
>How do I add cells from multiple worksheets?
>Thanks!!
Thanks for the advice. Yes, the totals of each category are in the same cell
on the different sheets. I just tried your suggestion and I get an error --
the cell reads "#NAME?". Perhaps I should mention that I named the sheets.
Do I need to use the names of the sheets instead of Sheet 1, etc? I tired
this and still get the error. Advice?
"Gord Dibben" wrote:
> If the totals on each worksheet are in the same cell you can set up a 3D
> reference to them on one sheet.
>
> =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
> on that sheet.
>
> If the cells are randomly placed on each sheet it becomes more difficult.
>
>
> Gord Dibben MS Excel MVP
>
> On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>
> >I have created an Excel file with a worksheet for each month's expenses. I
> >would like to have a final worksheet to sum all the different categories.
> >How do I add cells from multiple worksheets?
> >Thanks!!
>
>
If your sheet names have spaces, you must add quotes to the names.
So formula would read =SUM('your sheet:my sheet'!H34)
Try this trick.
Insert a new worksheet at beginning.
Name it Start
Insert a new worksheet at end.
Name it End
Then where you want the formula =SUM(Start:End!H34)
This will SUM H34 on all sheets between Start and End
With this method, you can insert more new sheets between without having to
change the formula.
Gord
On Sat, 16 Dec 2006 15:15:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>Thanks for the advice. Yes, the totals of each category are in the same cell
>on the different sheets. I just tried your suggestion and I get an error --
>the cell reads "#NAME?". Perhaps I should mention that I named the sheets.
>Do I need to use the names of the sheets instead of Sheet 1, etc? I tired
>this and still get the error. Advice?
>
>"Gord Dibben" wrote:
>
>> If the totals on each worksheet are in the same cell you can set up a 3D
>> reference to them on one sheet.
>>
>> =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
>> on that sheet.
>>
>> If the cells are randomly placed on each sheet it becomes more difficult.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>>
>> >I have created an Excel file with a worksheet for each month's expenses. I
>> >would like to have a final worksheet to sum all the different categories.
>> >How do I add cells from multiple worksheets?
>> >Thanks!!
>>
>>
thank u
extremely good
if u have any other tricks could u please email tome i would be most grateful
gerry letangg@yahoo.com
"Gord Dibben" wrote:
> If your sheet names have spaces, you must add quotes to the names.
>
> So formula would read =SUM('your sheet:my sheet'!H34)
>
> Try this trick.
>
> Insert a new worksheet at beginning.
>
> Name it Start
>
> Insert a new worksheet at end.
>
> Name it End
>
> Then where you want the formula =SUM(Start:End!H34)
>
> This will SUM H34 on all sheets between Start and End
>
> With this method, you can insert more new sheets between without having to
> change the formula.
>
>
> Gord
>
>
> On Sat, 16 Dec 2006 15:15:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>
> >Thanks for the advice. Yes, the totals of each category are in the same cell
> >on the different sheets. I just tried your suggestion and I get an error --
> >the cell reads "#NAME?". Perhaps I should mention that I named the sheets.
> >Do I need to use the names of the sheets instead of Sheet 1, etc? I tired
> >this and still get the error. Advice?
> >
> >"Gord Dibben" wrote:
> >
> >> If the totals on each worksheet are in the same cell you can set up a 3D
> >> reference to them on one sheet.
> >>
> >> =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
> >> on that sheet.
> >>
> >> If the cells are randomly placed on each sheet it becomes more difficult.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
> >>
> >> >I have created an Excel file with a worksheet for each month's expenses. I
> >> >would like to have a final worksheet to sum all the different categories.
> >> >How do I add cells from multiple worksheets?
> >> >Thanks!!
> >>
> >>
>
>
How can I add values from 2 separate worksheets. The values are all located
in different cells on both sheets.
Thank you.
"Gord Dibben" wrote:
> If the totals on each worksheet are in the same cell you can set up a 3D
> reference to them on one sheet.
>
> =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
> on that sheet.
>
> If the cells are randomly placed on each sheet it becomes more difficult.
>
>
> Gord Dibben MS Excel MVP
>
> On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>
> >I have created an Excel file with a worksheet for each month's expenses. I
> >would like to have a final worksheet to sum all the different categories.
> >How do I add cells from multiple worksheets?
> >Thanks!!
>
>
='Some sheet name here'!a1 + 'Some other sheet name here'!x99
Change the names of the sheets and the addresses of the cells.
Diana M wrote:
>
> Dear to whom it may concern,
>
> How can I add values from 2 separate worksheets. The values are all located
> in different cells on both sheets.
>
> Thank you.
>
> "Gord Dibben" wrote:
>
> > If the totals on each worksheet are in the same cell you can set up a 3D
> > reference to them on one sheet.
> >
> > =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
> > on that sheet.
> >
> > If the cells are randomly placed on each sheet it becomes more difficult.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
> >
> > >I have created an Excel file with a worksheet for each month's expenses. I
> > >would like to have a final worksheet to sum all the different categories.
> > >How do I add cells from multiple worksheets?
> > >Thanks!!
> >
> >
If cells are randomly dispersed you are stuck with manually creating
formulas.
Is there any sort of pattern you can pick up on to make it easier?
Gord
On Wed, 20 Aug 2008 09:40:15 -0700, Diana M <Diana
M@discussions.microsoft.com> wrote:
>Dear to whom it may concern,
>
>How can I add values from 2 separate worksheets. The values are all located
>in different cells on both sheets.
>
>Thank you.
>
>"Gord Dibben" wrote:
>
>> If the totals on each worksheet are in the same cell you can set up a 3D
>> reference to them on one sheet.
>>
>> =SUM(Sheet1:Sheet10!H34) where H34 on each sheet holds a total for a category
>> on that sheet.
>>
>> If the cells are randomly placed on each sheet it becomes more difficult.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Sat, 16 Dec 2006 12:43:01 -0800, BCC <BCC@discussions.microsoft.com> wrote:
>>
>> >I have created an Excel file with a worksheet for each month's expenses. I
>> >would like to have a final worksheet to sum all the different categories.
>> >How do I add cells from multiple worksheets?
>> >Thanks!!
>>
>>
Question
Reply
Top
Answer
Notify me of replies