Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Form vba help | Excel Discussion (Misc queries) | |||
How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions | |||
In MS Excel, after you input an USD amount, an option for automat. | Excel Worksheet Functions | |||
can you input time (hh:mm:ss) without having to input the colon i. | Excel Discussion (Misc queries) | |||
Conditional Format With SUMIF | Excel Worksheet Functions |