| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation question
hi gurus
I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Hi!
Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Hello
Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Post the actual formula for WS_Name so I can do some testing.
Biff "anny" wrote in message ... Hello Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Hi Biff
Here's the named formula WS_Name you requested =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32) It properly identifies the name of the worksheet when placed in a cell. Thanks for your efforts Anny "Biff" wrote in message ... Post the actual formula for WS_Name so I can do some testing. Biff "anny" wrote in message ... Hello Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
OK...........
I don't know why: =C1=--MID(WS_name,2,1) won't work. It works just fine when I test it in cells on the worksheet itself but when applied as the formula in data validation it doesn't work. I suspect it has something to do with the named formula. This WILL work: =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1) Or, put this formula in some out of the way cell like AA1: =--MID(WS_name,2,1) and then refer to that cell: =C1=AA1 Side note: =VALUE(MID(WS_Name,2,1)) =--MID(WS_Name,2,1) do the exact same thing. Biff "anny" wrote in message ... Hi Biff Here's the named formula WS_Name you requested =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32) It properly identifies the name of the worksheet when placed in a cell. Thanks for your efforts Anny "Biff" wrote in message ... Post the actual formula for WS_Name so I can do some testing. Biff "anny" wrote in message ... Hello Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Biff RULES!
anny |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
It's because if you use for instance indirect or offset that refers to
another cell (in this case I believe it is A1) you need to uncheck ignore blanks in the validation window, I think =C1=--MID(WS_Name,2,1) will work as validation if you do that. -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Biff" wrote in message ... OK........... I don't know why: =C1=--MID(WS_name,2,1) won't work. It works just fine when I test it in cells on the worksheet itself but when applied as the formula in data validation it doesn't work. I suspect it has something to do with the named formula. This WILL work: =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1) Or, put this formula in some out of the way cell like AA1: =--MID(WS_name,2,1) and then refer to that cell: =C1=AA1 Side note: =VALUE(MID(WS_Name,2,1)) =--MID(WS_Name,2,1) do the exact same thing. Biff "anny" wrote in message ... Hi Biff Here's the named formula WS_Name you requested =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32) It properly identifies the name of the worksheet when placed in a cell. Thanks for your efforts Anny "Biff" wrote in message ... Post the actual formula for WS_Name so I can do some testing. Biff "anny" wrote in message ... Hello Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation question
Hi Peo!
Hmmm..... Yes, unchecking Ignore blanks did allow the shorter formula to work. However, the long formula: =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1) Still contains Indirect calls and does work with Ignore blanks checked. Biff "Peo Sjoblom" wrote in message ... It's because if you use for instance indirect or offset that refers to another cell (in this case I believe it is A1) you need to uncheck ignore blanks in the validation window, I think =C1=--MID(WS_Name,2,1) will work as validation if you do that. -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Biff" wrote in message ... OK........... I don't know why: =C1=--MID(WS_name,2,1) won't work. It works just fine when I test it in cells on the worksheet itself but when applied as the formula in data validation it doesn't work. I suspect it has something to do with the named formula. This WILL work: =C1=--MID(MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32),2,1) Or, put this formula in some out of the way cell like AA1: =--MID(WS_name,2,1) and then refer to that cell: =C1=AA1 Side note: =VALUE(MID(WS_Name,2,1)) =--MID(WS_Name,2,1) do the exact same thing. Biff "anny" wrote in message ... Hi Biff Here's the named formula WS_Name you requested =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32) It properly identifies the name of the worksheet when placed in a cell. Thanks for your efforts Anny "Biff" wrote in message ... Post the actual formula for WS_Name so I can do some testing. Biff "anny" wrote in message ... Hello Biff - I couldn't get this to work. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Also, I'm not sure that the cell needs to be refered to as you've indicated. When I simply use =1 as the validation criteria in column C, it works fine. Also, the named function works properly. When I write =VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just can't get this expression to work as a validation criteria. Any ideas out there? Anny "Biff" wrote in message ... Hi! Well, I'm assuming that your named formula works properly. You have to refer to the cell that is being validated: =C1=MID(WS_Name,2,1) Now, the MID function returns TEXT, so if: =MID(WS_Name,2,1) Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 = rejected! So, try this: =C1=--MID(WS_Name,2,1) Or, format the target cell as TEXT (don't know if you really want to do that, though!) Biff "anny" wrote in message ... hi gurus I have defined a Name for a function, WS_Name, that returns the name of the active worksheet (eg A6-X, A4-R, B7-Q ...etc). On each worksheet, I want to add validation to column C. The value in colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above, column C should accept ONLY values of 6, 4 and 7 respectively. I can't get the validation to work. I'm trying ... DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1) or DataValidationSettings Allow: Custom, Formula: =VALUE(MID(WS_Name,2,1)) No luck so far. Column C still takes any value. Any ideas? TQ, Anny |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data validation question | New Users to Excel | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
| Another Exciting Data Table Question!!!! | Charts and Charting in Excel | |||
| DATA VALIDATION IN REVERSE #2 (FOR JULIE D.) | Excel Discussion (Misc queries) | |||
Linear Mode
