Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
It is not clear what you are trying to do, what do you want to be able to do or not to do if H1 holds Agency or Employee Traveler ? Because validation works by allowing only the TRUE values (if they are typed) -- Regards, Peo Sjoblom "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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
Thank you, T. Valko! It works perfectly.
"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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
You will need a macro to do this. Let's review *exactly* how you want this
to work. If H1 = Agency or Employee Traveler then allow the user to enter a number only in H2. If H1 does not = Agency or Employee Traveler then clear cell H2 and do not allow the user to enter anything in cell H2? -- Biff Microsoft Excel MVP "excel-chump" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
That is correct.
"T. Valko" wrote: You will need a macro to do this. Let's review *exactly* how you want this to work. If H1 = Agency or Employee Traveler then allow the user to enter a number only in H2. If H1 does not = Agency or Employee Traveler then clear cell H2 and do not allow the user to enter anything in cell H2? -- Biff Microsoft Excel MVP "excel-chump" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data validation
I see you started another thread on this. It looks like Gord's macro will do
what you want. -- Biff Microsoft Excel MVP "excel-chump" wrote in message ... That is correct. "T. Valko" wrote: You will need a macro to do this. Let's review *exactly* how you want this to work. If H1 = Agency or Employee Traveler then allow the user to enter a number only in H2. If H1 does not = Agency or Employee Traveler then clear cell H2 and do not allow the user to enter anything in cell H2? -- Biff Microsoft Excel MVP "excel-chump" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |