Sum a range of workbooks?
 

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

that was supposed to be

=SUM(Alloc1:Alloc3!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

try a formula like

=SUM(Alloc1:Alloc!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

Hey Duke,
thanks for the reply. I've tried all sorts of things, such as what you
suggested, but
the minute I type the : to signify a range I get an error. I'm not sure
it's possible to include a range of workbooks in a formula.

"Duke Carey" wrote:

that was supposed to be

=SUM(Alloc1:Alloc3!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

Here's what Excel's help file has to say about 3D formulas (ones that cover
several sheets):

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is
called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an
opening parenthesis.

Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.


"cottage6" wrote:

Hey Duke,
thanks for the reply. I've tried all sorts of things, such as what you
suggested, but
the minute I type the : to signify a range I get an error. I'm not sure
it's possible to include a range of workbooks in a formula.

"Duke Carey" wrote:

that was supposed to be

=SUM(Alloc1:Alloc3!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

I have a similar situation, however instead of summing the worksheets, i'd
like have a specific line from each worsheet (the total line) on a separate
worksheet (ie worksheet 1, line 5; then below that, worksheet 2, line 5;
worksheet 3, line 5, etc.). Is there any way to automate this?

"Duke Carey" wrote:

Here's what Excel's help file has to say about 3D formulas (ones that cover
several sheets):

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is
called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an
opening parenthesis.

Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.


"cottage6" wrote:

Hey Duke,
thanks for the reply. I've tried all sorts of things, such as what you
suggested, but
the minute I type the : to signify a range I get an error. I'm not sure
it's possible to include a range of workbooks in a formula.

"Duke Carey" wrote:

that was supposed to be

=SUM(Alloc1:Alloc3!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!

Sum a range of workbooks?
 

On my site there are a few examples
http://www.rondebruin.nl/tips.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Golf Nut" wrote in message ...
I have a similar situation, however instead of summing the worksheets, i'd
like have a specific line from each worsheet (the total line) on a separate
worksheet (ie worksheet 1, line 5; then below that, worksheet 2, line 5;
worksheet 3, line 5, etc.). Is there any way to automate this?

"Duke Carey" wrote:

Here's what Excel's help file has to say about 3D formulas (ones that cover
several sheets):

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is
called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an
opening parenthesis.

Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.


"cottage6" wrote:

Hey Duke,
thanks for the reply. I've tried all sorts of things, such as what you
suggested, but
the minute I type the : to signify a range I get an error. I'm not sure
it's possible to include a range of workbooks in a formula.

"Duke Carey" wrote:

that was supposed to be

=SUM(Alloc1:Alloc3!B8)

"cottage6" wrote:

A user has 4 separate files with the 4th file being a summary file; Alloc1,
Alloc2, Alloc3 and Summary files. The Alloc files have numbers in the same
corresponding cells she wants to add together to get the summary numbers.
What she wants to do is use a formula to sum the range of workbooks such as
=SUM(Alloc1!B8:Alloc3!B8). Can this be done somehow? I tried using named
ranges in the Summary sheet to no avail. Maybe with script? Yes, I know you
can use plus signs to add the 3 cells together, but this user is a bit "wants
things her own way". TIA!