Accessing sub-totals automatically (Excel 2K/Windows 2K)

Topic

New Reply

First thing

Reply | Quote

Replies

Why not invest in Quicken or Microsoft Money as a budget control tool?They offer many additional features you might otherwise have to invent yourself, such as reports pre-formatted with lots of period options-month/week/quarter ,etc. They also can track credit cards and combine charged and cash expenses into one report. If you wish to continue using Excel to do this, check around Google for excel checkbooks or something….there might be a template out there that does what you want. Good luck and have a nice retirement.

Reply | Quote

Hi everyone
Thanks for your helpful replies. Sorry it’s taken me so long to get back.
Torquemada: Pay money – aaaaaaaaaargh! Seriously, it’s not worth me purchasing one of these as we are only playing with this – it’s not a legal requirement for us! Actually, I’m only being lazy – the system we have works well enough and you might think I had enough time once a month to sort the things out manually anyway, so I’m going to stick with Excel for the moment, thanks – and thanks for your good wishes about retirement.
Brooke: Thanks for taking the time to produce your example. (I note from your personal details that you are an accountant; hence the mastery of Excel.) It’s interesting and shows up the fact that I don’t understand the built in Excel commands like SUMIF. I need to get to grips with these so that’s a little project I have in hand.
Steve: A PivotTable! Of course! Since you replied, I have been trying PTs and I have to say that the Excel help on these is not as helpful as I think it could be. I have progressed to the point where I realised that it was worth taking note of options presented by the wizard instead of just clicking NEXT, so once I found the ‘multiple consolidation ranges’ option, I got a lot further with summing more than one month’s worth of data. It’s still not quite right, so I’m still experimenting, but I think I am going to go with PTs as the solution. I tried producing a PT of PTs, but that got a little hairy , but I’ll continue to practice.
As usual, loungers come up trumps.
Thanks all for your help.

Reply | Quote

Hi everyone
Thanks for your helpful replies. Sorry it’s taken me so long to get back.
Torquemada: Pay money – aaaaaaaaaargh! Seriously, it’s not worth me purchasing one of these as we are only playing with this – it’s not a legal requirement for us! Actually, I’m only being lazy – the system we have works well enough and you might think I had enough time once a month to sort the things out manually anyway, so I’m going to stick with Excel for the moment, thanks – and thanks for your good wishes about retirement.
Brooke: Thanks for taking the time to produce your example. (I note from your personal details that you are an accountant; hence the mastery of Excel.) It’s interesting and shows up the fact that I don’t understand the built in Excel commands like SUMIF. I need to get to grips with these so that’s a little project I have in hand.
Steve: A PivotTable! Of course! Since you replied, I have been trying PTs and I have to say that the Excel help on these is not as helpful as I think it could be. I have progressed to the point where I realised that it was worth taking note of options presented by the wizard instead of just clicking NEXT, so once I found the ‘multiple consolidation ranges’ option, I got a lot further with summing more than one month’s worth of data. It’s still not quite right, so I’m still experimenting, but I think I am going to go with PTs as the solution. I tried producing a PT of PTs, but that got a little hairy , but I’ll continue to practice.
As usual, loungers come up trumps.
Thanks all for your help.

Reply | Quote

Why not invest in Quicken or Microsoft Money as a budget control tool?They offer many additional features you might otherwise have to invent yourself, such as reports pre-formatted with lots of period options-month/week/quarter ,etc. They also can track credit cards and combine charged and cash expenses into one report. If you wish to continue using Excel to do this, check around Google for excel checkbooks or something….there might be a template out there that does what you want. Good luck and have a nice retirement.

Reply | Quote

Silverback,

Here’s a little something to play with. Not much automation to worry about, I’m afraid. The subtotals are pulled in using sumifs, and the categories on each sheet are restricted using data validation linked to the named range on the totals sheet – to add a new category, you can simply insert a column anywhere between the current columns C to I. I kept the monthly sheets to a minimum for size reasons – all you’ll need to do after inserting a new sheet and labelling it, eg “mar”, is to copy the february formulae down and replace the “feb” in the formulae with “mar”.

This approach does mean you don’t have your subtotals on the monthly sheets, but hopefully it will get you started.

Reply | Quote

Silverback,

Here’s a little something to play with. Not much automation to worry about, I’m afraid. The subtotals are pulled in using sumifs, and the categories on each sheet are restricted using data validation linked to the named range on the totals sheet – to add a new category, you can simply insert a column anywhere between the current columns C to I. I kept the monthly sheets to a minimum for size reasons – all you’ll need to do after inserting a new sheet and labelling it, eg “mar”, is to copy the february formulae down and replace the “feb” in the formulae with “mar”.

This approach does mean you don’t have your subtotals on the monthly sheets, but hopefully it will get you started.

Reply | Quote

Have you tried a pivot table? Pivot table is meant for summarizing data in master table into subcategories and will grow and expand as needed.
Select the data table and data-pivot table report for the wizard.

Steve

Reply | Quote

Have you tried a pivot table? Pivot table is meant for summarizing data in master table into subcategories and will grow and expand as needed.
Select the data table and data-pivot table report for the wizard.

Steve

Reply | Quote