Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Solution sought for Budget calculations
I hope someone can help me with the approach to add a feature I want to
create on a spreadsheet for tracking purrchases. The worksheet is to be used to track purchases, such as supplies, Materials, toys, sand. There is a column where the user can assign a category to the purchase that indicates the purpose of the purchase. There's another column where an account code is entered. The categories and account codes are entered via dynamic named lists that is assigned as a drop down in the cells of the rows where the purchases are entered. I want to set up an area where the user can budget a dollar amount for specific account codes or categories. Then the I want to create a foumula that will sum the appropriate purchase amounts and deduct that from the budgeted amount. I know how to do this for both account code or category independently. But when a purchase entry matches both the category AND account code, the amount is deducted twice. For example this is what happens: Budget area: Category Budget remaining Books 300 275 Blocks 250 205 AccountCode Budget remaining 6155 400 375 6220 350 275 In purchase rows: Category AccountCode Expense Books 6155 25 Blocks 6220 45 Sand 6220 30 For the Books entry, the $25 is deducted from both the Books category budget AND the 6155 account budget. And the Blocks entry is deducted from the Blocks category AND 6220 Account. Are there any brilliant ideas of how to code the 'remaining' cells so each purchase amount will only be deducted once? If it's even possible, I would have the priority to be to deducted the purchase from the accountcode budget if the purchase matches both the accountcode and category. I've thought that maybe autofilter can come into play that the budget amounts are entered and the remaining cells are only in play when the autofilter selects the budget critera. I would appreciate any ideas. Thanks a bunch. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum If in a Budget | Excel Discussion (Misc queries) | |||
Proper function fix sought | Excel Discussion (Misc queries) | |||
Divide Monthly Sales Budget to Day Budget | Excel Worksheet Functions | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
how do i sought coloured number cells from normal ones? | Excel Discussion (Misc queries) |