I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee Traveler"
is chosen after the previous steps have been taken? Clearly the field can be
cleared manually, but I want to take out the human error of forgetting to go
back and change this.

-------------------------------
"T. Valko" wrote:

Try this:

Select H2
Goto DataValidation
Allow: Custom
Formula: =AND(OR($H$1="Agency",$H$1="Employee Traveler"),ISNUMBER($H$2))
Uncheck: Ignore blank
OK

Format H2 as CURRENCY

--
Biff
Microsoft Excel MVP


"excel-chump" wrote in message
...
I'm not sure I explained it properly. I want to replace the "0.5" in the
statement with an option that allows ANY number, in currency format, to be
entered in cell H2 (H2 is where the data validation function is located.)
The function I listed below is currently working. Would a wildcard allow
the
user to input data in H2?

"JNW" wrote:

Go to the Error message tab. Would unchecking the error option do what
you
are looking for?

If not, what cell is the validation in?
--
JNW


"excel-chump" wrote:

I'm trying to set up a function in "data validation", "other".

=IF(OR(H1="Agency",H1="Employee Traveler")," ",0.5)

So far this works. However, I want to modify it to allow the user to
input
any amount if it returns false. Is there a way to do this within the
function above or do I need to delve more into VBA?

Any amount of help would be grand.

Not possible using validation, you would need either a formula in H2 or an
event macro. The latter might be the best solutions but there are too many
idiots in this world to idiot proof against.


--


Regards,


Peo Sjoblom



"excel-chump" wrote in message
...
I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee
Traveler"
is chosen after the previous steps have been taken? Clearly the field can
be
cleared manually, but I want to take out the human error of forgetting to
go
back and change this.

-------------------------------
"T. Valko" wrote:

Try this:

Select H2
Goto DataValidation
Allow: Custom
Formula: =AND(OR($H$1="Agency",$H$1="Employee Traveler"),ISNUMBER($H$2))
Uncheck: Ignore blank
OK

Format H2 as CURRENCY

--
Biff
Microsoft Excel MVP


"excel-chump" wrote in message
...
I'm not sure I explained it properly. I want to replace the "0.5" in
the
statement with an option that allows ANY number, in currency format, to
be
entered in cell H2 (H2 is where the data validation function is
located.)
The function I listed below is currently working. Would a wildcard
allow
the
user to input data in H2?

"JNW" wrote:

Go to the Error message tab. Would unchecking the error option do
what
you
are looking for?

If not, what cell is the validation in?
--
JNW


"excel-chump" wrote:

I'm trying to set up a function in "data validation", "other".

=IF(OR(H1="Agency",H1="Employee Traveler")," ",0.5)

So far this works. However, I want to modify it to allow the user to
input
any amount if it returns false. Is there a way to do this within
the
function above or do I need to delve more into VBA?

Any amount of help would be grand.

I'm very new to macros and so far I've only been able to cut and paste
portions to get any results in that arena. Do you have any suggestions
regarding a macro to accomplish this? I can't put a formula in H2 b/c I need
to allow a number input if the previous criteria is met and that would erase
any formula in H2. thank you for your help!

"Peo Sjoblom" wrote:

Not possible using validation, you would need either a formula in H2 or an
event macro. The latter might be the best solutions but there are too many
idiots in this world to idiot proof against.


--


Regards,


Peo Sjoblom



"excel-chump" wrote in message
...
I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee
Traveler"
is chosen after the previous steps have been taken? Clearly the field can
be
cleared manually, but I want to take out the human error of forgetting to
go
back and change this.

-------------------------------
"T. Valko" wrote:

Try this:

Select H2
Goto DataValidation
Allow: Custom
Formula: =AND(OR($H$1="Agency",$H$1="Employee Traveler"),ISNUMBER($H$2))
Uncheck: Ignore blank
OK

Format H2 as CURRENCY

--
Biff
Microsoft Excel MVP


"excel-chump" wrote in message
...
I'm not sure I explained it properly. I want to replace the "0.5" in
the
statement with an option that allows ANY number, in currency format, to
be
entered in cell H2 (H2 is where the data validation function is
located.)
The function I listed below is currently working. Would a wildcard
allow
the
user to input data in H2?

"JNW" wrote:

Go to the Error message tab. Would unchecking the error option do
what
you
are looking for?

If not, what cell is the validation in?
--
JNW


"excel-chump" wrote:

I'm trying to set up a function in "data validation", "other".

=IF(OR(H1="Agency",H1="Employee Traveler")," ",0.5)

So far this works. However, I want to modify it to allow the user to
input
any amount if it returns false. Is there a way to do this within
the
function above or do I need to delve more into VBA?

Any amount of help would be grand.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$H$1" And Target.Value < "Agency" _
And Target.Value < "Employee Traveler" Then
Target.Offset(1, 0).Value = ""

End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 20 Sep 2007 09:56:01 -0700, excel-chump
wrote:

I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee Traveler"
is chosen after the previous steps have been taken? Clearly the field can be
cleared manually, but I want to take out the human error of forgetting to go
back and change this.

Thank you so much, Gordon and Peo Sjoblom! Your help has been invaluable to
me.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$H$1" And Target.Value < "Agency" _
And Target.Value < "Employee Traveler" Then
Target.Offset(1, 0).Value = ""

End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 20 Sep 2007 09:56:01 -0700, excel-chump
wrote:

I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee Traveler"
is chosen after the previous steps have been taken? Clearly the field can be
cleared manually, but I want to take out the human error of forgetting to go
back and change this.