Automaticaly set to default after date passed

[djfatboyfats]

I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this

Thanks

DJ

[Portuga]

My excel version (2003) allows me to insert formulas, (even when validation is on) if the formula result is the exact same value (its case sensitive) as the ones in the validation.

you CAN have the formula

=if(A1>TODAY();"DEFAULT";"")

If DEFAULT is is your validation list.

[djfatboyfats]

No i cant get it in and i am using 2003, i have attached a copy of the funding sheet, i would appreciate it if someone could put this in for me please,

I actully need it to default only if the date has passed and there is no figure in column G

Thanks in advance

DJ

[nrage21]

The problem with your requirement and the data validation in use, is that if the formula meets your condition (g = empty and due date < today), then the data validation would not set the DEFAULTED value for you... you would still need to select it from the list as this would be the only option.

The formula would be something like:

Please Login or Register  to view this content.

Obviously, I think what you are looking for is for excel to evaluate and set the value for you automatically, the data validation won't help you there. If you could provide the contiditions for the other 4 categories, perhaps we could help you (Monthly; Pending; PPP; Cash)

[djfatboyfats]

These options are just what type of deal it is, PPP (payment of 6 months), monthly (ongoing deal) Pending (Not a deal yet but in pending procedure)

If you can have a fiddle about with my example and see if we can make anything work

Thanks

DJ

[nrage21]

How would you determine what type of deal it is from your monthly sheet?

That is the ? you should think about

For this to work you would need to tie your data to another worksheet or range containing info about the different deal types. Possibly a column with the client ID and the deal type. Then in your monthly sheet you could have an additional column for the client ID

Let us know how you would like to proceed.

[Portuga]

Hi djfatboyfats,

See attched

Probable error. You inserted "DEFAULT" instead of "DEFAULTED"

[djfatboyfats]

ok lets explain, we are a debt collection company, we would speak to a customer with arrers and do a deal with them to pay the account, if the customer can pay the account within 1 - 6 months this is classed as a PPP if any longer then 6 months this would be a monthly arrangment, each member of staff manually enters this data on the worksheet for each month (like the sheet i sent you) and use the drop down box to determine if it is a PPP or a monthly arrangmement, when using the drop down box this will report to a summary and identify what is PPP payments and what is monthly, it will also read what has defaulted.

Any more information do not hessitate to contact me.

Thanks

DJ

[nrage21]

Portuga,
I stand corrected

Your formula needs a tiny adjustment

Please Login or Register  to view this content.

such as

Please Login or Register  to view this content.

because if the due date is greater than today, then there is no way the account has defaulted.

[djfatboyfats]

Ok sorry to keep bugging you, but i still cannot get this formula in with the data validation on, how do i get around this?

[nrage21]

djfatboyfats,

Portuga entered the information in the cell itself, not on the data validation. Refer to the zip file posted by Portuga. If you want to view the formula do not click on the drop-down, simply select the cell.

[Portuga]

You cant have those 2 validation criteria for the same cell. Either one or the other (LIST OR FORMULA).

The formula IN the cell will return what you want automatically and the validation will still allow for the list of possible values.

Note
(Once you insert a value from the validation list, the formula will be erased)

If you want, you can also insert this formula in the cell (in case there is no data in column D)

=IF(D12="","",IF(AND(G12="",D12<TODAY()),"DEFAULTED",""))

[djfatboyfats]

So there is no way to keep this in the same cell,

Example, set to PPP then date passes with no payment in column G your saying it will not automatically change to Defaulted.

Maybe VBA may work

Any ideas?

DJ

[Portuga]

You can add an extra column that states the Payment condition. (where you can have the drop down list) and in the status, you can have the formula)

See attached

[Portuga]

Correct attachment now

(You can add an extra column that states the Payment condition. (where you can have the drop down list) and in the status, you can have the formula)