ok... that does not seem to work.... Here is some more details that may
prove useful in figuring out what i meant....

Here are my worksheet names:
Summary.xls
Chad.xls
A.xls
b.xls
c.xls

They are all in the same folder on the same network share.

i want a formula that takes the result of:

=CONCATENATE(B5,".xls") Which is chad.xls and pulls the answer from
[chad.xls]Summary'!$D$10, and [a.xls]Summary'!$D$10 and
[b.xls]Summary'!$D$10... you get the idea.


Now two months later I hire "G" and create worksheet g.xls i would like to
be able to copy the formulas and paste them into an empty spot on the
spreadhseet and have the result appear without having to click-through some
2000 formulas.

Summary.xls is 1 VERY large workbook with nothing but formulas in it.
updating the formulas is getting too complicated and erronous.

Any thoughts?



"RagDyeR" wrote:

This formula will add the contents of cell B1, from Sheet1 to Sheet4:

=SUM(Sheet1:Sheet4!B1)

Now, *literally* this means it will sum *all* sheets starting at Sheet1 and
ending at Sheet4, and *anything in between*.
This means *physically* in between sheet tabs "1" and "4".

So, if you moved some sheets, and your tabs were in the order of:
1, 4, 2, 3,
Then Sheet2 and Sheet3 would *not* be included in the calculation.

If you had your tabs in the order of:
1, 25, 50, 4, 2, 3,
Then Sheets 25 and 50 would be added to Sheet1 and 4.

Carrying this a step further, some people keep 2 blank sheets named "start"
and "end" at the boundaries of their tabs, and add all new sheets between
them, so that a formula such as:

=SUM(Start:End!B1)
Will *automatically* include all the new sheets into the calculations.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"Chad" wrote in message
...
I am looking for a way to have a summary workbook and multiple identical
layout subbook that will automatically be included in a formula withough
having to change the formulas when a new subbook is added.

Example: Master Summary Workbook, and 3 Salesrep books. Then i hire
another rep. I want to duplicate the rep book and have it included in the
formulas. I have some 2,000 formulas in this sheet and i dont want to have
to
recreate it all the time.

In XL terminology, there are "Workbooks", and there are "Worksheets".

Workbooks *contain* worksheets.

You are talking about workbooks, *not* worksheets, even though your workbook
may only contain one worksheet.

Combine all your workbooks into one, and follow my suggestion above.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chad" wrote in message
...
ok... that does not seem to work.... Here is some more details that may
prove useful in figuring out what i meant....

Here are my worksheet names:
Summary.xls
Chad.xls
A.xls
b.xls
c.xls

They are all in the same folder on the same network share.

i want a formula that takes the result of:

=CONCATENATE(B5,".xls") Which is chad.xls and pulls the answer from
[chad.xls]Summary'!$D$10, and [a.xls]Summary'!$D$10 and
[b.xls]Summary'!$D$10... you get the idea.


Now two months later I hire "G" and create worksheet g.xls i would like to
be able to copy the formulas and paste them into an empty spot on the
spreadhseet and have the result appear without having to click-through some
2000 formulas.

Summary.xls is 1 VERY large workbook with nothing but formulas in it.
updating the formulas is getting too complicated and erronous.

Any thoughts?



"RagDyeR" wrote:

This formula will add the contents of cell B1, from Sheet1 to Sheet4:

=SUM(Sheet1:Sheet4!B1)

Now, *literally* this means it will sum *all* sheets starting at Sheet1
and
ending at Sheet4, and *anything in between*.
This means *physically* in between sheet tabs "1" and "4".

So, if you moved some sheets, and your tabs were in the order of:
1, 4, 2, 3,
Then Sheet2 and Sheet3 would *not* be included in the calculation.

If you had your tabs in the order of:
1, 25, 50, 4, 2, 3,
Then Sheets 25 and 50 would be added to Sheet1 and 4.

Carrying this a step further, some people keep 2 blank sheets named
"start"
and "end" at the boundaries of their tabs, and add all new sheets between
them, so that a formula such as:

=SUM(Start:End!B1)
Will *automatically* include all the new sheets into the calculations.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"Chad" wrote in message
...
I am looking for a way to have a summary workbook and multiple identical
layout subbook that will automatically be included in a formula withough
having to change the formulas when a new subbook is added.

Example: Master Summary Workbook, and 3 Salesrep books. Then i hire
another rep. I want to duplicate the rep book and have it included in the
formulas. I have some 2,000 formulas in this sheet and i dont want to have
to
recreate it all the time.

Thats not practical since each workbook contains 15 worksheets.

"RagDyeR" wrote:

In XL terminology, there are "Workbooks", and there are "Worksheets".

Workbooks *contain* worksheets.

You are talking about workbooks, *not* worksheets, even though your workbook
may only contain one worksheet.

Combine all your workbooks into one, and follow my suggestion above.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chad" wrote in message
...
ok... that does not seem to work.... Here is some more details that may
prove useful in figuring out what i meant....

Here are my worksheet names:
Summary.xls
Chad.xls
A.xls
b.xls
c.xls

They are all in the same folder on the same network share.

i want a formula that takes the result of:

=CONCATENATE(B5,".xls") Which is chad.xls and pulls the answer from
[chad.xls]Summary'!$D$10, and [a.xls]Summary'!$D$10 and
[b.xls]Summary'!$D$10... you get the idea.


Now two months later I hire "G" and create worksheet g.xls i would like to
be able to copy the formulas and paste them into an empty spot on the
spreadhseet and have the result appear without having to click-through some
2000 formulas.

Summary.xls is 1 VERY large workbook with nothing but formulas in it.
updating the formulas is getting too complicated and erronous.

Any thoughts?



"RagDyeR" wrote:

This formula will add the contents of cell B1, from Sheet1 to Sheet4:

=SUM(Sheet1:Sheet4!B1)

Now, *literally* this means it will sum *all* sheets starting at Sheet1
and
ending at Sheet4, and *anything in between*.
This means *physically* in between sheet tabs "1" and "4".

So, if you moved some sheets, and your tabs were in the order of:
1, 4, 2, 3,
Then Sheet2 and Sheet3 would *not* be included in the calculation.

If you had your tabs in the order of:
1, 25, 50, 4, 2, 3,
Then Sheets 25 and 50 would be added to Sheet1 and 4.

Carrying this a step further, some people keep 2 blank sheets named
"start"
and "end" at the boundaries of their tabs, and add all new sheets between
them, so that a formula such as:

=SUM(Start:End!B1)
Will *automatically* include all the new sheets into the calculations.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"Chad" wrote in message
...
I am looking for a way to have a summary workbook and multiple identical
layout subbook that will automatically be included in a formula withough
having to change the formulas when a new subbook is added.

Example: Master Summary Workbook, and 3 Salesrep books. Then i hire
another rep. I want to duplicate the rep book and have it included in the
formulas. I have some 2,000 formulas in this sheet and i dont want to have
to
recreate it all the time.

Then your best bet is to probably use code.

Try posting to the programming group.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Chad" wrote in message
...
Thats not practical since each workbook contains 15 worksheets.

"RagDyeR" wrote:

In XL terminology, there are "Workbooks", and there are "Worksheets".

Workbooks *contain* worksheets.

You are talking about workbooks, *not* worksheets, even though your
workbook
may only contain one worksheet.

Combine all your workbooks into one, and follow my suggestion above.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chad" wrote in message
...
ok... that does not seem to work.... Here is some more details that may
prove useful in figuring out what i meant....

Here are my worksheet names:
Summary.xls
Chad.xls
A.xls
b.xls
c.xls

They are all in the same folder on the same network share.

i want a formula that takes the result of:

=CONCATENATE(B5,".xls") Which is chad.xls and pulls the answer from
[chad.xls]Summary'!$D$10, and [a.xls]Summary'!$D$10 and
[b.xls]Summary'!$D$10... you get the idea.


Now two months later I hire "G" and create worksheet g.xls i would like to
be able to copy the formulas and paste them into an empty spot on the
spreadhseet and have the result appear without having to click-through
some
2000 formulas.

Summary.xls is 1 VERY large workbook with nothing but formulas in it.
updating the formulas is getting too complicated and erronous.

Any thoughts?



"RagDyeR" wrote:

This formula will add the contents of cell B1, from Sheet1 to Sheet4:

=SUM(Sheet1:Sheet4!B1)

Now, *literally* this means it will sum *all* sheets starting at Sheet1
and
ending at Sheet4, and *anything in between*.
This means *physically* in between sheet tabs "1" and "4".

So, if you moved some sheets, and your tabs were in the order of:
1, 4, 2, 3,
Then Sheet2 and Sheet3 would *not* be included in the calculation.

If you had your tabs in the order of:
1, 25, 50, 4, 2, 3,
Then Sheets 25 and 50 would be added to Sheet1 and 4.

Carrying this a step further, some people keep 2 blank sheets named
"start"
and "end" at the boundaries of their tabs, and add all new sheets
between
them, so that a formula such as:

=SUM(Start:End!B1)
Will *automatically* include all the new sheets into the calculations.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"Chad" wrote in message
...
I am looking for a way to have a summary workbook and multiple identical
layout subbook that will automatically be included in a formula withough
having to change the formulas when a new subbook is added.

Example: Master Summary Workbook, and 3 Salesrep books. Then i hire
another rep. I want to duplicate the rep book and have it included in
the
formulas. I have some 2,000 formulas in this sheet and i dont want to
have
to
recreate it all the time.