Restrict input if amount is exceeded
 

I'm using a checkbook style worksheet for a budget. I enter the total
budgeted amount in a single cell. As invoices come in, the amount is entered
in one column and then the adjusted total appears next to it. (All very
simple and straight forward). I would like to be able to restrict input once
the budgeted amount has been reached, i.e. if the budget is $500, my running
total is at $450, I receive an invoice for $60, if I try to enter $60 the
cell refuses the amount. BUT, I would like to make this even more
complicated by allowing the input if a code (or some such animal) is entered.
Does this even sound possible?
Thank you.

Assuming you have a table of budgeted amounts, named BudgetList, with
category in the first column, and amount in the second column, e.g.:

Cat Budget
Food 200
Travel 500
Lodging 400

With your worksheet set up as follows:

A B C D
1 Date Cat Amt Allow
2 4/1/2005 Food 50
3 4/1/2005 Food 50

Select cells C2:C55
Choose DataValidation
For Allow, select Custom
In the formula box, enter:
=OR(D2="X",SUMIF($B$1:$B2,B2,$C$1:$C2)<=VLOOKUP(B2 ,BudgetList,2,0))
Remove the check mark from 'Ignore blanks'
Click OK

Now, if you enter an X in column D, you'll be able to enter an amount
that will take you over budget.

This checks the running total, and may not prevent you from adjusting an
earlier entry that takes the current total over budget

Der Musensohn wrote:
I'm using a checkbook style worksheet for a budget. I enter the total
budgeted amount in a single cell. As invoices come in, the amount is entered
in one column and then the adjusted total appears next to it. (All very
simple and straight forward). I would like to be able to restrict input once
the budgeted amount has been reached, i.e. if the budget is $500, my running
total is at $450, I receive an invoice for $60, if I try to enter $60 the
cell refuses the amount. BUT, I would like to make this even more
complicated by allowing the input if a code (or some such animal) is entered.
Does this even sound possible?
Thank you.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html