automatically update budget list from source

[JRsoccer08]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a checkbook worksheet similar to that of the ledger template given in excel. This is because I want to keep track of my checking account transactions here, but then separate them by the categories I give them. For example, if I have these categories: Car, Food, Supplies, Housing, then I want four separate budget lists (one for each category) to keep track of the expenses categorically. I assume these would be on four separate worksheets in a workbook, concluding with five worksheets (one for the source checking account and four for the categories). So, I am pretty sure there is a formula I can use to accomplish this, but not sure exactly what. If anything isn't clear let me know. thanks.

 

[John McGhie]

You can do this as four different sheets, or as four pivot tables on the one
sheet.

The separate sheets method is conceptually simpler.

Make sure you have a column for "Category" in the main list, then copy the
entire main list.

Use Edit>Paste>Special and "Paste as link" on a new sheet. This copies the
whole list. Now turn on Autofilter and set that sheet to display only the
category you want to see.

Look up "Paste as link" and "Autofilter" in the Excel Help.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I created a checkbook worksheet similar to that of the ledger template given
in excel. This is because I want to keep track of my checking account
transactions here, but then separate them by the categories I give them. For
example, if I have these categories: Car, Food, Supplies, Housing, then I want
four separate budget lists (one for each category) to keep track of the
expenses categorically. I assume these would be on four separate worksheets
in a workbook, concluding with five worksheets (one for the source checking
account and four for the categories). So, I am pretty sure there is a formula
I can use to accomplish this, but not sure exactly what. If anything isn't
clear let me know. thanks.

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[JRsoccer08]

thanks a lot for the info. I think this will work well. I have been trying so many different complicated things with formulas and pivot tables, but this probably satisfies my intentions best ha. One quick question. When I paste the link to the cells I lose all of the cell formatting. If change some of the formatting on the sheet where i pasted the link, will the cells still update with any change in the source page. thanks again

 

[JRsoccer08]

actually I was just thinking and this will not work for what I ultimately want to do. Yes, I have the checkbook as the source I want to pull from as I said in the initial question, but ideally, I have two checkbook registers, a cash table, and two other account registers that I want to pull from as sources. All of these will have a "category" column as with the checkbook register. I want to do what I was describing originally (pull out all items in say, the "car" category and make a seperate list of them on another sheet. However, like I just said, I'd like the sheet with, for example, the "car" category list on it, to automatically update from any "car" transactions entered in any one of my five source worksheets. This is all intended to be in one workbook. Thank you again for your help. hope there's something i can make work for this

 

[John McGhie]

AutoFilter is the weapon you require. This will update automatically when
you add anything in any of the categories on the main list.

To bring the formatting in, first paste normally, THEN paste "as a link".
The first paste brings the formatting and values across, the second
substitutes reference formulas for each of the values.

The AutoFilter will then restrict the view to just the category you are
interested in.

Cheers

actually I was just thinking and this will not work for what I ultimately want
to do. Yes, I have the checkbook as the source I want to pull from as I said
in the initial question, but ideally, I have two checkbook registers, a cash
table, and two other account registers that I want to pull from as sources.
All of these will have a "category" column as with the checkbook register. I
want to do what I was describing originally (pull out all items in say, the
"car" category and make a seperate list of them on another sheet. However,
like I just said, I'd like the sheet with, for example, the "car" category
list on it, to automatically update from any "car" transactions entered in any
one of my five source worksheets. This is all intended to be in one workbook.
Thank you again for your help. hope there's something i can make work for this

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[JRsoccer08]

Thanks again for your help!! I think this may work without having to apply MANY complicated formulas, which is all I've been told up till now. Thanks.

AutoFilter is the weapon you require. This will update automatically when

 

[John McGhie]

Yeah Simple is good

You are actually going to use many complicated formulas, but Excel will
write them for you, automagically

Cheers

AutoFilter is the weapon you require. This will update automatically when

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]