D
DocBrown
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
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