Consolidate in Excel 2007 and 2003

[Diamontina Cocktail]

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 Govier]

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.

 

[Diamontina Cocktail]

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]

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.

 

[Diamontina Cocktail]

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]

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

 

[Diamontina Cocktail]

Hi

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.

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.

 

[Roger Govier]

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.

 

[Debra Dalgleish]

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