 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Validation List Query
Hi all, I've got a validation list showing a project problem status: New, Resolved, In Progress. These options are a named range 'Status'. Thus I do Data>Validation>Allow:List>Source:=Status No problem there. However, from time to time I need to input a custom status, which could be anything. The problem being that once I have a cell validated it does not allow me to type anything in it. Is there a means that keeps the validation drop down, but also lets me input my own data if, and when, I need to? I could always remove the validation from the individual cells, but hope someone might have a slightly less clunky solution. TIA, SamuelT  SignatureSamuelT Samuel, This may be way off-beam for you, but I will float it anyway. Assuming that the DV is in cell G4. Data>Validation>Allow:List>Source enter =IF(F4="",OFFSET(Status,0,0,(COUNTA(Status)),1),F4) Uncheck the Ignore blank box. When you OK, this will throw up an error, but just OK it. If F4 is blank, you will get you usual list. If you enter the alternate value you want to use in F4, when you select the dropdown next to the DV it will only show that new value. SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > Hi all, > [quoted text clipped - 15 lines] > > SamuelT You could still use the same Data Validation if you do this: Data>Validation Allow: List Source: =Status Select the Error Alert tab Style: Information Title: New Item Error Message: You entered an item that is not on the list. Click the [OK] button After doing that, if you enter a non-listed item...a message displays to alert you, but the new value is still accepted. Is that something you can work with? Regards, Ron SignatureRon Coderre Bit simpler than mine <vbg> If you uncheck the Show error alert button, it is completely unobtrusive (no validation of course, but hey you can't have everything). SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > You could still use the same Data Validation if you do this: > [quoted text clipped - 15 lines] > Regards, > Ron Simply increase the size of your "source" range to include an empty, blank cell at the bottom, and you'll allow *any* user input. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Hi all, I've got a validation list showing a project problem status: New, Resolved, In Progress. These options are a named range 'Status'. Thus I do Data>Validation>Allow:List>Source:=Status No problem there. However, from time to time I need to input a custom status, which could be anything. The problem being that once I have a cell validated it does not allow me to type anything in it. Is there a means that keeps the validation drop down, but also lets me input my own data if, and when, I need to? I could always remove the validation from the individual cells, but hope someone might have a slightly less clunky solution. TIA, SamuelT SignatureSamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=502434 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.