Excel help is rubbish!

Remove all ads!

I really need help with Excel 2007!

I have a workbook that has a sheet for each month of the year on it. Each sheet has a 'Total' column and I'm wanting to have a master/consolidated sheet that has a running total that adds up the other total columns.

The help section makes no sense to me what so ever so if anyone could tell me what to do (and remember that I'm an idiot) then I'd be really grateful!

 

I'm assuming that each monthly sheet is consistent as far as cell locations go. So if January has the Total in D8, so does Feb, March, etc. Let's assume that it is in D8.

In your Consolidation sheet, select the cell in which you want to place the running total. Click the "Sum" button on the right side of the Home ribbon. Click the tab for the first sheet (presumably January), and select the cell you want to want included in the consolidation (D8 in this example). Hold down the Shift key. Click the tab for the last sheet (presumably December). Release the Shift key, and hit Enter. You should have a formula that reads =SUM(January: December!D8) in your consolidation sheet (with the actual tab names and cell references applicable to your workbook).

Note that there shouldn't be any sheets that you don't want included between January and December.

Hope that helps.

 

Splunge has already given you good advice, so all I can add is this: Excel is all rubbish, not just its help

OK, so I can add another piece of advice: if your sheets are not consistent as far as cell locations, you can still do things manually. Do everything that Splunge said until you start selecting cells for the sum. Then, instead of using the Shift key, add the individual cells by Control-clicking on each Total cell separately, in each sheet.

 

I must be doing something wrong. Everytime I try it, the figure just goes to zero

 

Are you saying that the cell in your Consolidation sheet that you want the total in has a formula which returns the value 0, or that the content of the cell itself is 0?

If there is a formula, go into edit mode, highlight the entire formula, press Ctrl-C, and paste it into a reply in this thread; maybe we can help you debug it.

If the content of the cell itself is 0, then you're doing something wrong, and you'll need to describe precisely what steps you are doing. The steps I gave work (and I just tested them again to make sure).

I don't think Ziad's advice works if you're selecting multiple sheets; rather than Ctrl-click on each cell, you click on the first cell, enter a comma, go to the next sheet, click on the appropriate cell, enter a comma, etc. But if the cell locations aren't consistent, you don't need to use the Sum function - you can just add up individual cells.

 

First off; if you are using Excel as a database invest into Access; if you are using Excel for a true spreadsheet purpose than continue on my friend.

Splunge advice is very similar to what I would have given you.

I do recommend reviewing online help for Excel and I'm not talking about the built in help feature.

 

Excel works fine as a database for very simple purposes, though obviously access is better.

There is an easier way if your workbook doesn't have that many sheets in it. Though I must say that Splunge's way is the best and the easily most feasible one if you've a lot of sheets.

First, make sure that the totals are all on the same line in each column and in each sheet. Jan Col A totals at A10, Jan Col B totals at B10, Feb Col A totals at A10, etc. Don't worry about the columns being different sizes. Just pick a row way down with plenty of blank spaces so that all the columns will fit, then tell it to add up anything above it.

Now, go to the consolidated sheet. Select the cell you want Col A to add up in. Now manually add up all the Col A in the sheets. Hit "=", go to Jan and click on cell A10, hit "+", go to Feb and click on cell A10, hit "+" and so on till you've selected them all and then hit enter. This will give you the first column added up.

Now that that's done you just have to copy and paste that cell into all the ones across from it. If you picked A3 in the Consolidation sheet to add up the As, then B3 will add up all the Bs, etc. As you shift over one column Excel will automatically adjust the formula by one column.

That should be it. If that still doesn't work, or if you've a lot of sheets and need to use Splunge's method, I could do up an example workbook for you. You'd need to PM me your email, I don't think you can send attachments through Sorcerer's Place.

 

This may be a good time to tell you guys that I have no idea what you mean! I have trouble reading from a screen so I'm probably missing something important.

 

I can't remember if I still have you on MSN, otherwise I'd offer to help directly.

I presume that you have a total on each page for each month? Or do you literally have a column on each page and no single total?

Could you write out for us: Name of Sheet (Cell number of the Total for that month) For all the pages you are trying to add up?

For example: Jan (F15) or Sheet2 (G32)

 

Silvery, please don't take this the wrong way because I'm not trying to insult you, but how much experience do you have with Excel? I say this only because, given that you don't understand what we're talking about (ignoring the comments about Access, which are irrelevant the the issue at hand), it seems you're not very familiar with Excel - the things we're suggesting you try are pretty basic. If you're relatively inexperienced, and you think you're going to use it a fair amount in the future, it might be worthwhile taking an introductory one-day course (if you can afford it). Excel can be a very useful program, provided you know how to use it properly.

 

Better yet. Get the Excel 2007 For Dummies book. I bought it for my 70 year old dad, and if *he* can use Excel proficiently after reading though that book, then *anyone* can.