Data Validation
Folks,
i am using excel 2003 and i would like to set a number of cells so that users can only enter a date or N/A. i can set the date range but not the N/A... any ideas |
Data Validation
One way to do this: on a hidden tab or other remote spot in your
workbook, enter the date range in a column and add the N/A at the bottom or top. It might look like this: 11/8/2007 11/9/2007 11/10/2007 N/A Then create a named range called DateRange (or some similar unique name) and refer to those cells, including the N/A. When you go to set up the data validation, in the Allow box choose "List" and in the Source box enter the formula =DateRange. Will that work for you? DaveO Eschew obfuscation |
Data Validation
i had thought of using a list/range but the dates i need to enter run from
01/01/2000 to 31/12/2099 so the list will be huge, any other ideas?? "Dave O" wrote: One way to do this: on a hidden tab or other remote spot in your workbook, enter the date range in a column and add the N/A at the bottom or top. It might look like this: 11/8/2007 11/9/2007 11/10/2007 N/A Then create a named range called DateRange (or some similar unique name) and refer to those cells, including the N/A. When you go to set up the data validation, in the Allow box choose "List" and in the Source box enter the formula =DateRange. Will that work for you? DaveO Eschew obfuscation |
Data Validation
In the data validation dialog box, for Allow, choose Custom
In the Formula box, enter: =OR(B3="N/A",AND(B3=DATE(2000,1,1),B3<=DATE(2099,12,31)) ) where B3 is the address of the active cell. Manc Shaun wrote: i had thought of using a list/range but the dates i need to enter run from 01/01/2000 to 31/12/2099 so the list will be huge, any other ideas?? "Dave O" wrote: One way to do this: on a hidden tab or other remote spot in your workbook, enter the date range in a column and add the N/A at the bottom or top. It might look like this: 11/8/2007 11/9/2007 11/10/2007 N/A Then create a named range called DateRange (or some similar unique name) and refer to those cells, including the N/A. When you go to set up the data validation, in the Allow box choose "List" and in the Source box enter the formula =DateRange. Will that work for you? DaveO Eschew obfuscation -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com