Validation of "Petty Cash"

[Kaye]

Hi,

I have a column where the entries will be either of the following
four:

Petty Cash
LPOnnnn
Credit Card
Shell Card

The LPO has any four digits that will change, e.g "LPO1289"

It's imperative that"Petty Cash" is entered correctly as it's the
basis for further calculations using sumproduct. The other 3 entries
aren't that critical.

How can I ensure the user enters "Petty Cash" without any typo's - yet
also allow them the other 3 entries? Data Validation can't be
considered because the LPO number will always be different - or can it
somehow?

Regards, Kaye.

 

[Don Guillett]

This may work assuming they spell cash properly.
Right click sheet tab>view code>copy/paste this.Chg column number to suit
It could be further modified as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If InStr(UCase(Target), "CASH") > 0 Then Target = "Petty Cash"
End Sub

 

[Niek Otten]

<Data Validation can't be considered because the LPO number will always be different - or can it somehow?>

Insert a new sheet.
press F5 (GoTo) en go to cell A9999. Press and hold SFIFT and press CTRL+Arrow-up.

Enter this formula:

="LPO"&TEXT(ROW(),"0000")

And press CTRL+Enter (instead of just Enter)

Use this list in Data Validation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I have a column where the entries will be either of the following
| four:
|
| Petty Cash
| LPOnnnn
| Credit Card
| Shell Card
|
| The LPO has any four digits that will change, e.g "LPO1289"
|
| It's imperative that"Petty Cash" is entered correctly as it's the
| basis for further calculations using sumproduct. The other 3 entries
| aren't that critical.
|
| How can I ensure the user enters "Petty Cash" without any typo's - yet
| also allow them the other 3 entries? Data Validation can't be
| considered because the LPO number will always be different - or can it
| somehow?
|
| Regards, Kaye.
|
|
|

 

[Kaye]

Thanks so much Doug and Nick, both solutions work fine for me.

Kaye.