Just wondering about this -

I have sheets that are monthly ones which, at this moment, only started up
since I began to learn more about Excel, last month. I dragged my March
stuff from the Word doc I have it in to Excel, tidied it a bit and auto
summed the totals at the bottom. All well. April was done as the work was
done and auto sum totals and again all OK. Started into May and thought that
I would prefer Year to Date totals to auto update as I entered new data and
came across Consolidate and started a YTD sheet just for those totals. This
works well enough as I specified a particular cell for each of the totals I
am interested in and moved the totals of each sheet, to the same cell on
each monthly sheet and then consolidated the 3 sheets. So, at the moment my
YTD sheet actually DOES do what I want but the formula to consolidate
mentions ONLY those months March to May 2007 and nothing else. When I start
June I have to go back and add, into the consolidate formula, the June total
and the same for every other month of the year.

What I would like to do - as I am likely to forget to update consolidate -
is to set a formula in YTD sheet that would see, say "July 2007" sheet total
and all other months as I create them without me having to go back and
update the consolidate formula. So, if I decided to drag across Feb 2007
from Word to Excel the totals for that month would auto update the YTD
sheet. I actually WILL be going back in time and getting the data across
soon and this would help if that were possible.

So, does anyone know if it can be done? I believe the command to react in
the same way in both Excel 2003 and 2007 but if it makes any difference, I
use the 2007 version. Thanks.

Hi

One way you could do this would be to create two extra blank sheets in
your Workbook.
Rename them as First and Last.

On your Summary sheet, in cell B2 enter
=SUM(First:Last!B2)
(I am assuming row 1 contains headings and Column 1 contains headings)
Copy the formula across for as many columns and rows as you have in each
of your Monthly sheets

Now, drag the sheets to the following order (click on sheet tab and hold
left mouse button down as you drag to a new position, then release mouse
button)

Summary, First, March, April, May, Last

As you add more sheets, ensure that they are within the "sandwich" of
First and last and they will be summed.
If you wanted to see totals for a 3 month period, move First and Last so
they only encompass those three sheets.

Now, having established the principle of First and Last, I would
actually called them F and L to make their Tabs smaller, and change the
formula to
=SUM(F:L!B2)
Use colour for the F and L tabs so you can easily see what your summary
is including.

--
Regards

Roger Govier


"Diamontina Cocktail" wrote in message
...
Just wondering about this -

I have sheets that are monthly ones which, at this moment, only
started up since I began to learn more about Excel, last month. I
dragged my March stuff from the Word doc I have it in to Excel, tidied
it a bit and auto summed the totals at the bottom. All well. April was
done as the work was done and auto sum totals and again all OK.
Started into May and thought that I would prefer Year to Date totals
to auto update as I entered new data and came across Consolidate and
started a YTD sheet just for those totals. This works well enough as I
specified a particular cell for each of the totals I am interested in
and moved the totals of each sheet, to the same cell on each monthly
sheet and then consolidated the 3 sheets. So, at the moment my YTD
sheet actually DOES do what I want but the formula to consolidate
mentions ONLY those months March to May 2007 and nothing else. When I
start June I have to go back and add, into the consolidate formula,
the June total and the same for every other month of the year.

What I would like to do - as I am likely to forget to update
consolidate - is to set a formula in YTD sheet that would see, say
"July 2007" sheet total and all other months as I create them without
me having to go back and update the consolidate formula. So, if I
decided to drag across Feb 2007 from Word to Excel the totals for that
month would auto update the YTD sheet. I actually WILL be going back
in time and getting the data across soon and this would help if that
were possible.

So, does anyone know if it can be done? I believe the command to react
in the same way in both Excel 2003 and 2007 but if it makes any
difference, I use the 2007 version. Thanks.

Roger,

Thanks for your answer. Your formula was simplicity and that was what I
wanted. Unfortunately in Excel 2007 though the drag over the other sheets is
supposed to indicate that you want those sheets in the equation, it doesn't
work out right when you attempt it. So, you always end up with a formula
error. The only way around it is to manually enter the formula in the cell
and type out the sheet names, too.

I was attempting to get it to work with sheets I hadn't already made up,
ignoring sheets that weren't there and working with what were there and then
adding the other sheets in as I made up one for each month. That isn't
possible, I suppose. So, I just made up the forthcoming sheets and at the
cell I required the totals to be for each sheet, I entered 0 in there which
sufficed to give the answers I wanted. The result is what I was looking for
even if not in the intuitive way I wanted it.

Thanks again.

"Roger Govier" wrote in message
...
Hi

One way you could do this would be to create two extra blank sheets in
your Workbook.
Rename them as First and Last.

On your Summary sheet, in cell B2 enter
=SUM(First:Last!B2)
(I am assuming row 1 contains headings and Column 1 contains headings)
Copy the formula across for as many columns and rows as you have in each
of your Monthly sheets

Now, drag the sheets to the following order (click on sheet tab and hold
left mouse button down as you drag to a new position, then release mouse
button)

Summary, First, March, April, May, Last

As you add more sheets, ensure that they are within the "sandwich" of
First and last and they will be summed.
If you wanted to see totals for a 3 month period, move First and Last so
they only encompass those three sheets.

Now, having established the principle of First and Last, I would actually
called them F and L to make their Tabs smaller, and change the formula to
=SUM(F:L!B2)
Use colour for the F and L tabs so you can easily see what your summary is
including.

Hi

I think you must be doing something wrong.
It works perfectly in all versions of Excel.
The formula is only entered on the Summary sheet - nowhere else.
Sheets F and L have nothing at all on them.
What formulae you choose to have on any other sheets, is down to you,
but they would not include the formula I gave for the summary
=SUM(F:L!B2)

With the above formula on the summary sheet, AND Summary being outside
of F and L, the value of B2 from every sheet located between F and L
will be summed.
Dragging sheets in or out of the "sandwich" of F and L will have no
effect, nor will any sheets which are blank and have no data.

If you want to send me a copy of your workbook direct, I will set it up
as described and send back to you.
I assure, you it does work in XL2007.

To send direct, remove NOSPAM form my email address.

--
Regards

Roger Govier


"Diamontina Cocktail" wrote in message
...

Roger,

Thanks for your answer. Your formula was simplicity and that was what
I wanted. Unfortunately in Excel 2007 though the drag over the other
sheets is supposed to indicate that you want those sheets in the
equation, it doesn't work out right when you attempt it. So, you
always end up with a formula error. The only way around it is to
manually enter the formula in the cell and type out the sheet names,
too.

I was attempting to get it to work with sheets I hadn't already made
up, ignoring sheets that weren't there and working with what were
there and then adding the other sheets in as I made up one for each
month. That isn't possible, I suppose. So, I just made up the
forthcoming sheets and at the cell I required the totals to be for
each sheet, I entered 0 in there which sufficed to give the answers I
wanted. The result is what I was looking for even if not in the
intuitive way I wanted it.

Thanks again.

"Roger Govier" wrote in message
...
Hi

One way you could do this would be to create two extra blank sheets
in your Workbook.
Rename them as First and Last.

On your Summary sheet, in cell B2 enter
=SUM(First:Last!B2)
(I am assuming row 1 contains headings and Column 1 contains
headings)
Copy the formula across for as many columns and rows as you have in
each of your Monthly sheets

Now, drag the sheets to the following order (click on sheet tab and
hold left mouse button down as you drag to a new position, then
release mouse button)

Summary, First, March, April, May, Last

As you add more sheets, ensure that they are within the "sandwich" of
First and last and they will be summed.
If you wanted to see totals for a 3 month period, move First and Last
so they only encompass those three sheets.

Now, having established the principle of First and Last, I would
actually called them F and L to make their Tabs smaller, and change
the formula to
=SUM(F:L!B2)
Use colour for the F and L tabs so you can easily see what your
summary is including.

"Roger Govier" wrote in message
...
Hi

I think you must be doing something wrong.
It works perfectly in all versions of Excel.

If you mean the drag over to indicate different sheets in one workbook
should have cells at a particular location added together to go into this
sheet, no it doesnt in 2007 version. It always stuffs up. However, I used
what you said and have this: =SUM(JUNE07:MARCH07!F200) which works just
fine. Anything between March07 and June07 fall into line. I have yet to
incorporate more data as previous to using Excel I was doing it all on Word
docs and manually adding it. I have 0 in the required cell for June07 so it
all works out just the way I want it to.

The formula is only entered on the Summary sheet - nowhere else.

Yep, that is where I tried it. Still doesnt work unless you manually type
the formula in.

Sheets F and L have nothing at all on them.
What formulae you choose to have on any other sheets, is down to you, but
they would not include the formula I gave for the summary
=SUM(F:L!B2)


If you type it in like that, yes it works. If you hold the left mouse button
down and drag over each tab to highlight them, no it doesnt but it is
supposed to work that way.

Hi
no it doesnt in 2007 version. It always stuffs up

I am sorry to have to dispute you, but it does work in all versions of
Excel, including XL2007.
You have already proved it, if it works for
=SUM(JUNE07:MARCH07!F200)

I have 0 in the required cell for June07 so it all works out just the
way I want it to.
There doesn't need to be anything at all entered on any sheets, if you
haven't yet got to that month.

Still doesnt work unless you manually type the formula in.
I don't know what you mean by this.

As I offered before, if you want to send me a copy of the workbook, I
will take a look to see what is going on and send you back a working
copy.
To send direct remove NOSPAM from my email address

--
Regards

Roger Govier


"Diamontina Cocktail" wrote in message
...

"Roger Govier" wrote in message
...
Hi

I think you must be doing something wrong.
It works perfectly in all versions of Excel.

If you mean the drag over to indicate different sheets in one workbook
should have cells at a particular location added together to go into
this sheet, no it doesnt in 2007 version. It always stuffs up.
However, I used what you said and have this: =SUM(JUNE07:MARCH07!F200)
which works just fine. Anything between March07 and June07 fall into
line. I have yet to incorporate more data as previous to using Excel I
was doing it all on Word docs and manually adding it. I have 0 in the
required cell for June07 so it all works out just the way I want it
to.

The formula is only entered on the Summary sheet - nowhere else.

Yep, that is where I tried it. Still doesnt work unless you manually
type the formula in.

Sheets F and L have nothing at all on them.
What formulae you choose to have on any other sheets, is down to you,
but they would not include the formula I gave for the summary
=SUM(F:L!B2)


If you type it in like that, yes it works. If you hold the left mouse
button down and drag over each tab to highlight them, no it doesnt but
it is supposed to work that way.

"Roger Govier" wrote in message
...
Hi
no it doesnt in 2007 version. It always stuffs up

I am sorry to have to dispute you, but it does work in all versions of
Excel, including XL2007.
You have already proved it, if it works for
=SUM(JUNE07:MARCH07!F200)


You dont understand what I mean. What is typed above works but dragging over
doesnt. You are supposed to be able to hold the mouse down on a sheet tag
and drag over other sheet tags in order to indicate those highlighted sheets
as being included. That formula above is not the same thing as just dragging
the mouse over to achieve the same thing.

I have 0 in the required cell for June07 so it all works out just the way
I want it to.
There doesn't need to be anything at all entered on any sheets, if you
haven't yet got to that month.


I had to enter that. I had wanted it to add up for sheets that dont exist
and it errored. So, I added June07 and put 0 at the required spot and it
worked fine.

Hi

I'm sorry, but the technique does work. As I have said before, send me
your file and I will set it up for you.

--
Regards

Roger Govier


"Diamontina Cocktail" wrote in message
...

"Roger Govier" wrote in message
...
Hi
no it doesnt in 2007 version. It always stuffs up

I am sorry to have to dispute you, but it does work in all versions
of Excel, including XL2007.
You have already proved it, if it works for
=SUM(JUNE07:MARCH07!F200)


You dont understand what I mean. What is typed above works but
dragging over doesnt. You are supposed to be able to hold the mouse
down on a sheet tag and drag over other sheet tags in order to
indicate those highlighted sheets as being included. That formula
above is not the same thing as just dragging the mouse over to achieve
the same thing.

I have 0 in the required cell for June07 so it all works out just
the way I want it to.
There doesn't need to be anything at all entered on any sheets, if
you haven't yet got to that month.


I had to enter that. I had wanted it to add up for sheets that dont
exist and it errored. So, I added June07 and put 0 at the required
spot and it worked fine.

To include a group of sheets in the formula, type the first part of the
formula, e.g. =SUM(
Then, click on the tab of the first sheet, e.g. First
Hold the Shift key, and click on the tabl of the last sheet,e.g. Last
With the sheets selected, click on the cell that you want to sum, e.g. F200
Type the closing bracket, then press the Enter key


Diamontina Cocktail wrote:

"Roger Govier" wrote in message
...

Hi

no it doesnt in 2007 version. It always stuffs up


I am sorry to have to dispute you, but it does work in all versions of
Excel, including XL2007.
You have already proved it, if it works for
=SUM(JUNE07:MARCH07!F200)


You dont understand what I mean. What is typed above works but dragging
over doesnt. You are supposed to be able to hold the mouse down on a
sheet tag and drag over other sheet tags in order to indicate those
highlighted sheets as being included. That formula above is not the same
thing as just dragging the mouse over to achieve the same thing.

I have 0 in the required cell for June07 so it all works out just
the way I want it to.

There doesn't need to be anything at all entered on any sheets, if you
haven't yet got to that month.


I had to enter that. I had wanted it to add up for sheets that dont
exist and it errored. So, I added June07 and put 0 at the required spot
and it worked fine.




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html