I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

okay i tried what you said to do but I want the data to be combined for
example I want sheet 1 from July and sheet 1 from August to combine into one
sheet as if to hold two months worth of info on one. I honestly think there
is no hope and I am going to have to manually enter all the data, but if you
know of another way please help! thanks for the first suggestion very much!!
~Erin

"JLatham" wrote:

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

I tried what you said but I need the data to combine into one sheet. I need
the data from one month to add to another month and make one new sheet. Any
more suggestions? Please let me know, but thanks for the first suggestion

"JLatham" wrote:

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

You're far from in trouble. I'll describe briefly below, but for more
detailed help, use Excel Help and search for the topic "Linking Data" the
very first topic that probably will come up is "Create a link to another
cell, Workbook, or program". It gives lots of information on things you need
to know to get along with your project.

You can treat multiple workbooks just like multiple sheets in a single
workbook!

I presume you want the Quarterly layout to be similar to that of the monthly
sheets, with just different identification of the time period. I'd start by
copying the first workbook.xls file to another .xls file with a new name to
use as the quarterly file.
Lets call it Q1_2006.xls and we will name one sheet in it as Q1_06_Income.
Open that file and the other 3 files for the quarter (Jan.xls Feb.xls Mar.xls
for discussion) also. Now you have 4 workbooks open.
For just one example, you may have a place to display
Quarterly Income from Vehicle Loans: and you will record that in cell D1 on
the Q1_06_Income sheet.

and you have a related area in the sheet in each of the monthly workbooks
which is labeled "Monthly Income from Vehicle Loans: " The sheet with it is
named 'Income" in each of the monthly workbooks. It's probably at D1 also
but doesn't have to be.

The process: in the Q1_2006.xls file, choose D1 and then type in an = symbol
then click on the Jan.xls file and go to where the value is in that
workbook, as we said also in cell D1. Click D1 on that sheet and your
formula back in the Quarterly workbook will become something like
=[Jan.xls]Income!$D$1
press the + (addition) key and then choose the Feb.xls workbook and click on
the appropriate cell there, another + key and to the final workbook, Mar.xls
and click the cell in it that you need and press the enter key. Your formula
will be something like
=[Jan.xls]Income!$D$1+[Feb.xls]Income!$D$1+[Mar.xls]Income!$D$1 in the
Q1_2006.xls workbook and it will show the total from the other 3 workbooks.

Repeat as required for everything. When you close the 3 monthly workbooks,
the values will still remain. When someone opens the Q1_2006.xls workbook
later if the 3 monthly workbooks are available from their computer, no
problem, otherwise a message about not being able to update the links/linked
data will appear. They just reply with "use last good info" and they'll see
what was there the last time that the links were refreshed from the source
workbooks.

Now, after setting things up once for the first quarter's workbook, things
become easier. You can then copy that workbook as Q2_2006.xls when the time
comes, open it and Apr.xls, May.xls and June.xls and then use global Search
and replace to change the filenames in the Q2_2006.xls workbook!
Edit | Replace: Find = Jan.xls Replace = Apr.xls
Edit | Replace: Find = Feb.xls Replace = May.xls
Edit | Replace: Find = Mar.xls Replace = June.xls

and as long as the layouts of Apr, May and June workbooks are same as Jan,
Feb and Mar.xls workbooks are laid out the same, work is probably done!

There are other ways to update the links, but that's easiest to tell about
here.


"erinattbt123" wrote:

I tried what you said but I need the data to combine into one sheet. I need
the data from one month to add to another month and make one new sheet. Any
more suggestions? Please let me know, but thanks for the first suggestion

"JLatham" wrote:

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

THANK YOU SOOO MUCH! I followed your directions and my boss was very pleased
with the results. Thanks again !!

"JLatham" wrote:

You're far from in trouble. I'll describe briefly below, but for more
detailed help, use Excel Help and search for the topic "Linking Data" the
very first topic that probably will come up is "Create a link to another
cell, Workbook, or program". It gives lots of information on things you need
to know to get along with your project.

You can treat multiple workbooks just like multiple sheets in a single
workbook!

I presume you want the Quarterly layout to be similar to that of the monthly
sheets, with just different identification of the time period. I'd start by
copying the first workbook.xls file to another .xls file with a new name to
use as the quarterly file.
Lets call it Q1_2006.xls and we will name one sheet in it as Q1_06_Income.
Open that file and the other 3 files for the quarter (Jan.xls Feb.xls Mar.xls
for discussion) also. Now you have 4 workbooks open.
For just one example, you may have a place to display
Quarterly Income from Vehicle Loans: and you will record that in cell D1 on
the Q1_06_Income sheet.

and you have a related area in the sheet in each of the monthly workbooks
which is labeled "Monthly Income from Vehicle Loans: " The sheet with it is
named 'Income" in each of the monthly workbooks. It's probably at D1 also
but doesn't have to be.

The process: in the Q1_2006.xls file, choose D1 and then type in an = symbol
then click on the Jan.xls file and go to where the value is in that
workbook, as we said also in cell D1. Click D1 on that sheet and your
formula back in the Quarterly workbook will become something like
=[Jan.xls]Income!$D$1
press the + (addition) key and then choose the Feb.xls workbook and click on
the appropriate cell there, another + key and to the final workbook, Mar.xls
and click the cell in it that you need and press the enter key. Your formula
will be something like
=[Jan.xls]Income!$D$1+[Feb.xls]Income!$D$1+[Mar.xls]Income!$D$1 in the
Q1_2006.xls workbook and it will show the total from the other 3 workbooks.

Repeat as required for everything. When you close the 3 monthly workbooks,
the values will still remain. When someone opens the Q1_2006.xls workbook
later if the 3 monthly workbooks are available from their computer, no
problem, otherwise a message about not being able to update the links/linked
data will appear. They just reply with "use last good info" and they'll see
what was there the last time that the links were refreshed from the source
workbooks.

Now, after setting things up once for the first quarter's workbook, things
become easier. You can then copy that workbook as Q2_2006.xls when the time
comes, open it and Apr.xls, May.xls and June.xls and then use global Search
and replace to change the filenames in the Q2_2006.xls workbook!
Edit | Replace: Find = Jan.xls Replace = Apr.xls
Edit | Replace: Find = Feb.xls Replace = May.xls
Edit | Replace: Find = Mar.xls Replace = June.xls

and as long as the layouts of Apr, May and June workbooks are same as Jan,
Feb and Mar.xls workbooks are laid out the same, work is probably done!

There are other ways to update the links, but that's easiest to tell about
here.


"erinattbt123" wrote:

I tried what you said but I need the data to combine into one sheet. I need
the data from one month to add to another month and make one new sheet. Any
more suggestions? Please let me know, but thanks for the first suggestion

"JLatham" wrote:

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin

So I guess this means you'll have to show up for work again tomorrow, right?
So, maybe it wasn't such a big favor after all <g

Glad to hear it worked for you, and thanks very mych for the feedback.

"erinattbt123" wrote:

THANK YOU SOOO MUCH! I followed your directions and my boss was very pleased
with the results. Thanks again !!

"JLatham" wrote:

You're far from in trouble. I'll describe briefly below, but for more
detailed help, use Excel Help and search for the topic "Linking Data" the
very first topic that probably will come up is "Create a link to another
cell, Workbook, or program". It gives lots of information on things you need
to know to get along with your project.

You can treat multiple workbooks just like multiple sheets in a single
workbook!

I presume you want the Quarterly layout to be similar to that of the monthly
sheets, with just different identification of the time period. I'd start by
copying the first workbook.xls file to another .xls file with a new name to
use as the quarterly file.
Lets call it Q1_2006.xls and we will name one sheet in it as Q1_06_Income.
Open that file and the other 3 files for the quarter (Jan.xls Feb.xls Mar.xls
for discussion) also. Now you have 4 workbooks open.
For just one example, you may have a place to display
Quarterly Income from Vehicle Loans: and you will record that in cell D1 on
the Q1_06_Income sheet.

and you have a related area in the sheet in each of the monthly workbooks
which is labeled "Monthly Income from Vehicle Loans: " The sheet with it is
named 'Income" in each of the monthly workbooks. It's probably at D1 also
but doesn't have to be.

The process: in the Q1_2006.xls file, choose D1 and then type in an = symbol
then click on the Jan.xls file and go to where the value is in that
workbook, as we said also in cell D1. Click D1 on that sheet and your
formula back in the Quarterly workbook will become something like
=[Jan.xls]Income!$D$1
press the + (addition) key and then choose the Feb.xls workbook and click on
the appropriate cell there, another + key and to the final workbook, Mar.xls
and click the cell in it that you need and press the enter key. Your formula
will be something like
=[Jan.xls]Income!$D$1+[Feb.xls]Income!$D$1+[Mar.xls]Income!$D$1 in the
Q1_2006.xls workbook and it will show the total from the other 3 workbooks.

Repeat as required for everything. When you close the 3 monthly workbooks,
the values will still remain. When someone opens the Q1_2006.xls workbook
later if the 3 monthly workbooks are available from their computer, no
problem, otherwise a message about not being able to update the links/linked
data will appear. They just reply with "use last good info" and they'll see
what was there the last time that the links were refreshed from the source
workbooks.

Now, after setting things up once for the first quarter's workbook, things
become easier. You can then copy that workbook as Q2_2006.xls when the time
comes, open it and Apr.xls, May.xls and June.xls and then use global Search
and replace to change the filenames in the Q2_2006.xls workbook!
Edit | Replace: Find = Jan.xls Replace = Apr.xls
Edit | Replace: Find = Feb.xls Replace = May.xls
Edit | Replace: Find = Mar.xls Replace = June.xls

and as long as the layouts of Apr, May and June workbooks are same as Jan,
Feb and Mar.xls workbooks are laid out the same, work is probably done!

There are other ways to update the links, but that's easiest to tell about
here.


"erinattbt123" wrote:

I tried what you said but I need the data to combine into one sheet. I need
the data from one month to add to another month and make one new sheet. Any
more suggestions? Please let me know, but thanks for the first suggestion

"JLatham" wrote:

Unless some things are really complex somewhere, simple copying of the
worksheets from monthly workbooks into one to become the quarterly/annual
workbook should work.

Starting from ground zero?
Open a Monthly workbook, select all sheets in the workbook, use
Edit | Move or Copy Sheet
and in the dialog, at the top use the list to choose New Book
and be sure and tick the option at the bottom next to "Create a Copy"

The group of sheets will be copied into a new book, leaving your old one in
one piece. Now save that new book as Quarterly1 or whatever and save it.
Leave it open, open another month's book and repeat the Move or Copy Sheet
with Create a Copy option from the month's book but choosing the Quarterly1
workbook as the destination.

Same basic process to create the annual workbook.


"erinattbt123" wrote:

I do a monthly 3 sheet profit and loss sheet in excel for my employers
company and now he wants me to make a quarterly and yearly sheet combining 3
workbooks (or 12 for the yearly) into one. Is there any way to achieve this
by merging workbooks andif so how???? Please help!!!
Thanks Erin