Excel/Consolidating categories of expenditure into separate sheets
QUESTION: Hi Aidan
My spreadsheet consists of one column containing income, one column containing all expenditure and eleven columns splitting up the different categories of expenditure. At the moment, if I need to work with only one particular category, I do a customised sort, selecting the relevant column. Unfortunately this doesn't work very well. Is there a way in which one can create eleven separate sheets, containing the eleven categories of expenditure that are automatically updated as one populate the eleven columns?
ANSWER: It would be possible to use VBA to do something like this, but I'm always reluctant to suggest solutions that effectively involve duplicating data - I would suggest using a final column which concatenated the expense columns - something like
This would then give you a column you could auto-filter on, using the contains custom filter choice.
---------- FOLLOW-UP ----------
QUESTION: I'm not quite sure about this Aidan. Can you elaborate? My spread sheet currently has: Column A = all income; column B = all expenditure, then in columns C to M = the different categories already captured under column B (but this data is not very readable because it is chronologically arranged according to the entries in column B with lots of empty cells in between. (I basically need these entries to be automatically sorted with the empty cells eliminated without having to do a customised sort every time). So if I follow your suggestion, do I not end up with a column similar to my current column B?
Or is the best solution not to create eleven macros and do the sort using keyboard shortcuts? The problem with this is just the length of the entries in the columns that expand constantly as the sheet is populated.
As I say, it would be possible to create extra sheets, but I'm always worried about solutions that duplicate data - my problem is one of understanding the requirements here - it might help if I could see a sample file if that is possible? My email is email@example.com
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here