automatically update budget list from source

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.
0
JRsoccer08
1/11/2010 4:44:05 PM
mac.office.excel 1146 articles. 0 followers. Follow

6 Replies
601 Views

Similar Articles

[PageSpeed] 9

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


On 12/01/10 3:44 AM, in article 59bb0982.-1@webcrossing.JaKIaxP2ac0,
"JRsoccer08@officeformac.com" <JRsoccer08@officeformac.com> wrote:

> 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:john@mcghie.name


0
John
1/11/2010 8:38:19 PM
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
0
JRsoccer08
1/12/2010 4:16:21 PM
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 &quot;category&quot; column as with the checkbook register.  I want to do what I was describing originally (pull out all items in say, the &quot;car&quot; 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 &quot;car&quot; category list on it, to automatically update from any &quot;car&quot; 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
0
JRsoccer08
1/12/2010 4:25:11 PM
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


On 13/01/10 3:25 AM, in article 59bb0982.2@webcrossing.JaKIaxP2ac0,
"JRsoccer08@officeformac.com" <JRsoccer08@officeformac.com> wrote:

> 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:john@mcghie.name


0
John
1/12/2010 9:05:22 PM
>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.  <br><br>AutoFilter is the weapon you require.  This will update automatically when <br>
> you add anything in any of the categories on the main list. <br>
>  <br>
> To bring the formatting in, first paste normally, THEN paste &quot;as a link&quot;. <br>
> The first paste brings the formatting and values across, the second <br>
> substitutes reference formulas for each of the values. <br>
>  <br>
> The AutoFilter will then restrict the view to just the category you are <br>
> interested in. <br>
>  <br>
> Cheers <br>
>  <br>
>  <br>
> On 13/01/10 3:25 AM, in article 59bb0982.2@webcrossing.JaKIaxP2ac0, <br>
> &quot;JRsoccer08@officeformac.com&quot;  wrote: <br>
>  <br>
> > actually I was just thinking and this will not work for what I ultimately want <br>
> > to do.  Yes, I have the checkbook as the source I want to pull from as I said <br>
> > in the initial question, but ideally, I have two checkbook registers, a cash <br>
> > table, and two other account registers that I want to pull from as sources. <br>
> > All of these will have a &quot;category&quot; column as with the checkbook register.  I <br>
> > want to do what I was describing originally (pull out all items in say, the <br>
> > &quot;car&quot; category and make a seperate list  of them on another sheet.  However, <br>
> > like I just said, I'd like the sheet with, for example, the &quot;car&quot; category <br>
> > list on it, to automatically update from any &quot;car&quot; transactions entered in any <br>
> > one of my five source worksheets.  This is all intended to be in one workbook. <br>
> > Thank you again for your help. hope there's something i can make work for this <br>
>  <br>
> This email is my business email -- Please do not email me about forum <br>
> matters unless you intend to pay! <br>
>  <br>
>  --  <br>
>  <br>
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer, <br>
> McGhie Information Engineering Pty Ltd <br>
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 <br>
> +61 4 1209 1410, mailto:john@mcghie.name <br>
>  <br>
>  <br>
>
0
JRsoccer08
1/13/2010 10:14:25 PM
Yeah :-)  Simple is good :-)

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

Cheers


On 14/01/10 9:14 AM, in article 59bb0982.4@webcrossing.JaKIaxP2ac0,
"JRsoccer08@officeformac.com" <JRsoccer08@officeformac.com> wrote:

>> 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
>> 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 
>>  
>>  
>> On 13/01/10 3:25 AM, in article 59bb0982.2@webcrossing.JaKIaxP2ac0,
>> "JRsoccer08@officeformac.com"  wrote:
>>  
>>> 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:john@mcghie.name
>>  
>>  
>> 

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:john@mcghie.name


0
John
1/15/2010 8:49:12 AM
Reply:

Similar Artilces:

CEditCtrl with automatic line feed
Hello NG, I hope someone can help. I already have been searching in the web but without success. I use a CEditCtrl, in which one can input several lines. When the input reaches the border, I want the text to "jump" into the next line, so that the user is not supposed to scroll (like here in my mail editor e.g.). Do you know if such a EditCtrl exists or how I can do it? Thanks in advance Guido "Guido Franzke" <guidof73@yahoo.de> wrote in message news:eZcKcnTbHHA.4720@TK2MSFTNGP04.phx.gbl... > Hello NG, > > I hope someone can help. I already have been se...

Automatic Charts in Excel
Hello !!! I have an external program that generates data files in CSV format Column A contains my data and Column E contain time of day values i 24hr:min:sec format (22:12:30) The CSV gets updated every 10 second and no 2 rows have the same time value. After the CSV is closed I nee to do the following... Open a worksheet (excel 2000) with a macro already loaded. Run the macro which prompts the user to load a CSV file. Search column E for the time value 22:00:00 If not found then select E1 Search column E for the time value 04:00:00 If not found select the last cell in column E Create a range...

Sharing a master calendar and updating in Outlook..
How Can i share a master calendar on outlook where everyone of our employees (12) can access and update? Is it possible on Outlook to do this. We are on an exchange server. Yes - create a public calendar folder, and set permissions on it as you wish. I wouldn't make more than a couple of people 'owners' - David wrote: > How Can i share a master calendar on outlook where > everyone of our employees (12) can access and update? Is > it possible on Outlook to do this. We are on an exchange > server. ...

Can't create new Recipient Update Service in Exchange 2000
Running Exchange 2000 sp3 on Windows 2000 server sp4 I'm trying to create a new Recipient Update Service (for my child domain). I can right click on the Recipient Update Services folder, select new and follow the process through to the end, but when I click finish it pops the error: Access is denied Facility: Win3 ID no: c007000 Exchange System Manage Now all of the research I've done about this problem says to make sure the user acount trying to make the change is allowed Exchange Full Administrator rights and is a member of the local administrator group on the Exchange server. I h...

email in queue duplicate itself automatically
I set a queue in the crm with an CRMEmailEnabled mailbox. When a message come in the queue, this message will duplicate itself in the CRM queue every 4 minutes and every 27 minutes. Did someone experience the same problem ? -- Christophe Hi Christophe I had a similar problem and it was caused by a problem with the mail box for the disabled user, this caused e mails to be resent and thus multi copies to appear. The dealys in the copies was cuaed by the re try timeouts on the mail routers. I hope this helps Biffio "cla" wrote: > I set a queue in the crm with an CRMEmail...

Outlook 2003 starts automatically although not in startup folder
Running XP home, with Office 2003. When starting my laptop, Outlook 2003 starts even though it is not in the Startup folder or in the startup tab from msconfig. Why does it startup? I don't want it to startup automatically. I cannot find any reference to this and have not received any replys from any MS guru. Please help. Charles Chuck <anonymous@discussions.microsoft.com> wrote: > Running XP home, with Office 2003. When starting my > laptop, Outlook 2003 starts even though it is not in the > Startup folder or in the startup tab from msconfig. Why > does ...

contacts appear as deleted in my list when they didn't delete me
I have this situation come up every once in a while is anyone else experiencing this? and what is causing the miss labeling? -- Four Generations Of Trust And Betrayal...One Legacy Skywolfe Hi Skywolfe, Just to confirm, how are you identifying them as being deleted? -- Jonathan Kay Microsoft MVP - Windows Live Messenger MSN Messenger/Windows Messenger MessengerGeek Blog: http://www.messengergeek.com Messenger Resources: http://messenger.jonathankay.com (c) 2009 Jonathan Kay - If redistributing, you must include this signature or citation -- "Rage Skywolfe"...

Export Contacts Table from MS Access to Global Address List in Exchange
Hi, I want to know if its possible to export my Contacts table in Microsoft Access to Global Address List on Exchange so I can view all the contacts in Outlook Address Book? Thanks, Eric It's possible, yes. But, not knowing the format of your Access database, I can't tell you much more. Look into the CSVDE tool. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" <eric.nguyen312@gmail.com> wrote in message news:1171476029.880250.152000@q2g2000cwa.googlegroups.com... > Hi, > I want to know if its possible to export my Contacts ...

Sorting a List #2
Hi, In column A I have a list of dollar values. $40 $-30 $10 $0 $0 $120 What I would like to do is in column B put a ranking 1through whatever corresponding to their dollar value so it would look like this: $40 2 $-30 6 $10 3 $0 4 $0 4 $120 1 I hope I explained it well. In other words, in column B, they are sorted according to rank right where they stand. $120 is number 1. $-30 is number 6, etc. Thanks, Tom In B1: =rank(a1,$a$1:$a$6) and copy down to B6 Tom G wrote: > > Hi, > > In column A I have a list of dollar values. > > $40 > $-30 &...

Whay does an update CRASH Access?
Running Access 2003 on XP, doing a select from a table and passing the results to this subroutine to update another table. When running in debug mode, it works. When I stop it an anaylze the update statement, copy and paste into a new query, it works. Running without debug turned on, Access crashes (on three different installations of Access). What I know to be true: 1. the input record set contains records, all fields are non-null 2. the datatypes are correct 3. it crashes on the first record Any clues? ---- Sub UpdLicenseGrantedOutput(rstOutput As Recordset) Dim SQL_...

Value list
i need help with making value lists in excel so that i can make my data base consistant with the data i am entering That's not much to go on. What is a value list? And what constitutes making your database consistent with the data you are entering? Post back and explain in detail what you have, what you want to do, and what you want to happen. Some examples are always good. HTH Otto "Maint Girl" <Maint Girl@discussions.microsoft.com> wrote in message news:4EDE3701-B704-4BE8-B449-58662FDCB495@microsoft.com... > i need help with making value lists in ...

Textbox in form that updates a table.
Is it possible for a textbox within a form to update a section within a table? If so how. Also I wanted to know is it possible to gray out a command button on a form after its clicked. Normally a textbox will update a single value in a single field in a table. To update several rows of a table, you can use an update query. The update query can use the value in the textbox on your form. To grey out a command button, you would set it's enabled property to false. Note: you must move focus to another control so that the command button doesn't have the focus at the time. Jeanette Cu...

updating two seperate spreadsheets with same info
I have 2 seperate spreadsheets one with monthly info and one with yearly info. I wondered if there was a way to update the same info in one spreadsheet and it automatically fill in in the other spreadsheet since its the same information going into both databases without having to copy/paste all the time why not have one where you filter for month ??? data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "T" <T@discussions.microsoft.com> wrote in message news:9E30B3EE-2D44-4E1D-8F10-16CC36DB8D6E@microsoft.com.....

update links without other worksheet open?
All of a sudden my Excel 2002 will not open a workbook that contains links to other workbooks unless the other workbook is open. I get a dialog saying links could not be updated and to edit them. Any suggestions from anyone on how to change settings so the linked workbooks do not need to be open to update? Thanks. It kind of sounds like the linked workbook has moved (or been deleted). Any chance that it isn't there anymore? Something to try... With the workbook closed, Tools|Options|Edit tab Check "Ask to update automatic links" Then file|open your workbook. You shou...

SBS 2008 Server Not Updating Properly
I am running Windows update an SBS 2008 box. The machine seems to download and install 39 updates, then it fails with the message: Some updated were not installed Restart now to finish installing updates. Succeded: 1 update Failed: 38 updates Error(s) found: Code 80070718 Windows update encountered an unknown error. Code 643 Windows update encountered an unknown error. I started with needing 43 updates, and each time it installs only 1. If I run it again, it will probably install 1 and tell me it failed to install 37 others. Please help. Thanks! ...

HR version number not updating after installing SP
Hello, We upgraded Great Plains & Human resources to 8.0 & also installed SP2. After the SP2, The Great Plains version number has properly updated to 8.00g34 but the HR version still says 8.00.7 instead of 8.00.10 on the server & other wrkstations. This was not a problem. The HR modified reports all upgraded fine & all workstations are able to run the modified HR reports except one wrkstn. This one wrkstn. has properly updated the HR version number to 8.00.10. When we try to run an HR report from this wrkstn, it says that the dictionary is not found. The reports need to ...

Exchange 5.5 server not updating Service Pack.
Windows 2000 Server with Exchange 5.5 loaded on it. Windows was at SP2, and Exchange was at SP1. Updated Windows to SP4, rebooted, all works well. Went to update Exchange to SP4, and it tells me that a newer version is already installed, and Update quits. Same thing happens with SP3 or SP4 for Exchange. I can check the Mad.exe and the Store.exe, and both versions report Version 6.0 Build 4712.7 (Service Pack 1). This does not make any sense to me. I know you do not have to install Exchange SP's before Windows, as I have loaded other Win2K boxes, updated to SP4 and then upgraded Exc...

This item has exceeded the budget for account number.
Hello, I am getting a recurring error message which prevents a user from entering in an expense into eReq because the system considers the account over budget, when in fact it is not. To correct the error, the origin of which we are still uncertain, we were instructed to run File/Maintenance/PO Enhancements/Reconcile. We received this message several times today and ran the utility which did not have any impact on the situation- the error message continues to pop up (i.e., for 53300-1650-000 Finance Office Supplies). The exact error message is: This item has exceeded the budget for...

Mailing lists
I would like to creat a mailing list, so I can keep my customers up to date on events and sales. Please help. Carol @Country Morning Livermore, Ca. sunni2003 wrote: > I would like to creat a mailing list, so I can keep my customers up to date on events and sales. Please help. > Carol @Country Morning > Livermore, Ca. > How many addies have you collected? just wondering (if I get a response hahaha) FYI ask Vanna for "another E" Tools, Mail & Catalog merge, create address list. If you have 2000 it is on the toolbar as "Mail Merge." -- Mary Sauer M...

How can I find automatic links in an Excel-sheet?
I have an Excel-sheet with "automatic links" to other workbooks. At least according to Excel, so every time when I open the document I have to click 'No' to the questions whether I want to update these. The problem is I can not find this/these automatic link/s. Is there a way to find all automatic links so that I can delete them? Tobias wrote: > I have an Excel-sheet with "automatic links" to other workbooks. At > least according to Excel, so every time when I open the document I > have to click 'No' to the questions whether I want to update th...

Populate text boxes from a list box
Hi, I have a form with text and combo boxes where user enters data. After saving, a list box in another form is being populated, and all that works just fine. What i wanted to do next is to enable users to open one set of data from the list box (one row of data) in that form with text and combo boxes. I tried and tried but either i manage to open that form in add mode where all fields are blank, or i manage to open the form with data from the list box, but always positioned on the first data in the list box. I have been trying for a while so it could be possible that i am missing...

Folders list
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Folders list has to be re-opened manually on startup from the Views menu every time. This problem is identical to one discussed in a string that ended last May without a solution. This started happening on both my computer - a new iMac - and on my wife's, a Macbook purchased last May. (The problem showed up first on hers; mine started a few weeks later.) Both computers are running Snow Leopard. Has anyone figured this out? This is a multi-part message in MIME format. ------------...

I put all addresses into dist. lists & want them back in contacts
In Outlook 2007, I created two distribution lists using all of my contacts, and then deleted all addresses from the address book contacts list. I thought I could still send individual messages to members of the distribution lists, but have not been able to do so. I think I need to reinstate all addresses to the contacts list but don't know how to do that. Turns out I don't really need the distribution lists after all -- I just want to be able to send email messages to individual contacts again, as it was before I created the two distribution lists. PLEASE HELP! Thank...

Year End Tax updates 2008
Can 2008 year end tax update for GP v.10 be applied prior to running the final payroll for the year? -- Lynette I am not sure about the US one but I assume it is the same as the Canadian one. With the Can Pay tax update the YE process fro payroll needs to be completed BEFORE the update has been applied. Hope this helps. -- Fliehigh "Lynette" wrote: > Can 2008 year end tax update for GP v.10 be applied prior to running the > final payroll for the year? > -- > Lynette Thanks for your response. I am not sure that it is that way with the US update. In the past...

Automatic update installed, but where is it?
Thank you Microsoft for installing the occasional updates to my Windows XP but I'll be more thankful if they also explain how to use what they have already installed. They installed on my machine something called: *Windows Malicious Software Removal Tool* which made me vary happy as my Symantec AV found few things it has quarantined but it says it can't remove them and I was hoping the Microsoft tool can remove them. So I am looking for this Microsoft removal tool that was supposedly installed on my machine but I don't know where they put it ! Does any one know...