combination validated list and free input

Two cells, first one "Name", the next a validated dropdown 
("Dept."), based on "Name".  "Dept" currently validates by 
Indirect(Name cell reference).  Each Name of employees is 
defined as a range that has their allowable departments.  
The challenge is that I want this to be able to handle non-
employees also.  So I need the validation to allow for a 
range name (the list of employees), but also a "Non-
Employee" entry, which would allow for someone outside of 
the company to submit one of these forms.

My one idea of how to do this is to have one extra cell 
below Name, which is conditionally formatted in a way that 
waits for the Name cell to populate with "non-employee" 
and then springs to life by turning yellow with blue text 
saying "Enter name here".  That way, until someone selects 
from the Name dropdown that they are "non-employee", the 
form seems normal.  Once a "non-employee" is selected, 
they have to provide the name then.

Is there a "cleaner" way of doing this, if it makes sense 
at all what I am after?
0
anonymous (74722)
11/17/2004 5:52:55 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
297 Views

Similar Articles

[PageSpeed] 59

Hi
I would create a userform for this to handle the validation more easily. 

"boris" wrote:

> Two cells, first one "Name", the next a validated dropdown 
> ("Dept."), based on "Name".  "Dept" currently validates by 
> Indirect(Name cell reference).  Each Name of employees is 
> defined as a range that has their allowable departments.  
> The challenge is that I want this to be able to handle non-
> employees also.  So I need the validation to allow for a 
> range name (the list of employees), but also a "Non-
> Employee" entry, which would allow for someone outside of 
> the company to submit one of these forms.
> 
> My one idea of how to do this is to have one extra cell 
> below Name, which is conditionally formatted in a way that 
> waits for the Name cell to populate with "non-employee" 
> and then springs to life by turning yellow with blue text 
> saying "Enter name here".  That way, until someone selects 
> from the Name dropdown that they are "non-employee", the 
> form seems normal.  Once a "non-employee" is selected, 
> they have to provide the name then.
> 
> Is there a "cleaner" way of doing this, if it makes sense 
> at all what I am after?
> 
0
frank.kabel (11126)
11/17/2004 6:28:05 AM
In the Data Validation dialog box for the Name cell, select the Error 
Alert tab. Remove the check mark from 'Show error alert after invalid 
data is entered'

In a cell in the workbook, type "N/A" (or the message you'd like to
    appear when a non-employee name is entered)
Name this cell, e.g.   DeptNA,

In the Dept cell, use a Data Validation formula that refers to this range:

   =IF(COUNTIF(NameList,B1),INDIRECT(B1),DeptNA)

where NameList is the range that contains the employee names, and B1 is 
the Name cell.

boris wrote:
> Two cells, first one "Name", the next a validated dropdown 
> ("Dept."), based on "Name".  "Dept" currently validates by 
> Indirect(Name cell reference).  Each Name of employees is 
> defined as a range that has their allowable departments.  
> The challenge is that I want this to be able to handle non-
> employees also.  So I need the validation to allow for a 
> range name (the list of employees), but also a "Non-
> Employee" entry, which would allow for someone outside of 
> the company to submit one of these forms.
> 
> My one idea of how to do this is to have one extra cell 
> below Name, which is conditionally formatted in a way that 
> waits for the Name cell to populate with "non-employee" 
> and then springs to life by turning yellow with blue text 
> saying "Enter name here".  That way, until someone selects 
> from the Name dropdown that they are "non-employee", the 
> form seems normal.  Once a "non-employee" is selected, 
> they have to provide the name then.
> 
> Is there a "cleaner" way of doing this, if it makes sense 
> at all what I am after?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/17/2004 12:37:04 PM
Reply:

Similar Artilces:

Outlook 2003 Imap Auto Purge wish list
I hope someone at Microsoft can code in a auto purge Imap deleted email function into there next Service Pack. I can see a problem rolling this out to our employees. There going to delete messages and those messages are going to stay on the Email server until they do a Purge. I'm afraid they just won't do this. We use Imap. No auto purge means emails staying on the server which over time just takes up hard drive space and makes tape backups take longer. wayne Hi Wayne, You should send suggestions to mswish@microsoft.com as the newsgroups are not monitored for feature request...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

[ANN] "Bend Word to your Will" -- April 2005 edition free download
Dear all, THIS ANNOUNCEMENT IS ABOUT A *NON-COMMERCIAL* RESOURCE BASED IN PART ON ACCUMULATED ADVICE FROM THIS NEWSGROUP The latest version of my notes on Word -- titled "Bend Word to Your Will" -- is now available for downloading from the Word MVPs' site, http://www.mvps.org/word/FAQs/WordMac/Bend/BendWord.htm WHAT IS "BEND WORD TO YOUR WILL"? "Bend Word to Your Will" is not a commercial product for sale -- it's free. It's a dictionary-style (mainly) set of notes on Word that I've been continually adding to and polishing for the past four year...

how can I asign printscreen to a function key or key combination?
My key board has no printscreen key. What can I do? That seems unlikely, but it is hardly a Word issue. The PrtScn button is usually a dual function button somewhere on the top row of your keyboard. If it is not then you need to ask the keyboard manufacturer. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< ...

Input mask & Format question
Greetings I use the input mask \(999") "999\-9999 in the Phone field of my table. I wanted the brackets, space and dash stored in the table because the data is imported into other applications. 1234567890 is stored as (123) 456-7890. On one of my forms there is a combobox with fields CustID, CustName, Phone. My problem is how to format the phone number in the cbo. It shows up as 1234567890. Thanks in advance Becky Hi - Set the input mask to \(999") "999\-9999:0 (adding semicolon - zero). This forces the literals to be stored along with the other characters...

SetKeyboardState to send Ctrl key combinations
Can anyone help me understand how to use the SetKeyboardState function. I have tried several examples I found online but none of them (nor any of my own attempts) work for sending keyboard combinations such as Ctrl+A. Here is the code I currently have that does not work Private Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" ( _ ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long Privat...

Sending multiple Emails so each person does not get the list
We want to send out multiple emails to several email accounts. We dont want the accounts to see the list of accounts that the email went to. How does one do this in Outlook? In any contacts folder, Tools | Mail Merge is the best choice. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "tom" <Spamblocker@ameritech.net> wrote in message = ...

User list
I'm wondering how to check the users on Exchange Server 2003, I tried it on E2k3 by [PS] W:\>Get-Mailbox -server Exchange2k3, and I got nothing in the list, but when I tried to remove Exchange Server 2003, I got an error, The component "Microsoft Exchange Messaging and Collaboration Services" cannot be assigned the action "Remove" because: - One or more users currently use a mailbox store on this server. These users must be moved to a mailbox store on a different server or be mail disabled before uninstalling this server. Any idea? Thanks. I followed the instruc...

Validation Problem #2
I have a schema file PDDSch.xsd which validates the PDD.xml file. the rootnodes are as shown below PDD.xml <PDD xmlns="http://tempuri.org/PDDSch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance" > PDDSch.xsd <xs:schema id="PDD" targetNamespace="http://tempuri.org/PDDSch.xsd" xmlns="http://tempuri.org/PDDSch.xsd" xmlns:mstns="http://tempuri.org/PDDSch.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="quali...

How to remove locations from the drop-down list?
My drop down list for appointment locations contains a few duplicate entries, usually because one of the duplicates contains a spelling error. How can I get rid of these false entries so that I don't always have to be super-cautious to pick the correct one? M. "Michael Moser" <michael.nospam.moser@nospam.freesurf.ch> wrote in message news:DF17B5F2-DA57-48A7-B7DD-3B5A52F2A4E6@microsoft.com... > My drop down list for appointment locations contains a few duplicate > entries, usually because one of the duplicates contains a spelling error. > How ...

Distribution list #7
How do I load a distribution list into the address book under a single heading ...

edit a drop down list
Hello, I have 2 questions 1. for an existing drop down list, how can I add a new entry? That is, a list named "division" now contains 5 items, and I want to add a sixth. I've tried to highlight all 6 items and typing "division" into the name box, but that just jumps me to the original 5 names. I also tried highlighting the original 5 and deleting the "division" from the name box, but again no luck. 2. Can I enlarge the font on a drop down list, so it appears larger when the arrow is clicked? The font is very small at present. To change the name r...

Schedule+ Free Busy System Folders "Access Denied"
Have you guys seen issues where full exchange administrators are unable to delete empty schedule+ free busy folders from system manager....they are constantly prompted for authentication I'm working on an Exchange 2003 SP2 site consolidation and migration from 10 exchange 5.5 sites to 2 Exchange 2003 Admin Groups...using the site consolidation features, the free busy folders from the consolidated sites remain in the Org, but they have empty replica lists due to the consolidated mailboxes now have new legacyexchangedn and use just the two admin groups free busy folders. So, I have ...

Customer Mailing List
Whats the best way to output the customer mailing list so that it doesn't require the entire content to be reformatted before printing out mailing labels? If exporting to a word .doc it lumps everything up in one column. Is there a better way w/out having to virtually reformat/retype all of the customers? Thanx in advance! Rich Export as a CSV, then use Mail Merge to load the fields. -- Jason Hunt Advanced Computer Systems You can use word mail merge to directly link to the database. No need to run a csv file. mt "Jason Hunt" <jhunt@advcs.ca> wrote in mess...

multiple pick from drop-down list
Does anyone know how to setup a drop-down type (or other type) list where I could pick multiple selections from the list and have it populate each selection in a different cell? Thanks, John Drop-downs do not lend themselves to multiple selections (what should be shown in the text area when the drop-down is not dropped down? You would probably be best off using a ListBox (it supports multiple selections). Can you give a more detailed description of what you want to do? Rick "johnrb7865" <johnrb7865@discussions.microsoft.com> wrote in message news:DD85C389-6AFC-4BC1-9...

Holiday listing
How do I get Publisher 2003 to automatically add major holidays to each month? ...

input box prompt
Hi, Once again I need help. I have an Input Box that I would like to set a portion of the prompt font to bold. I've searched the forums and could not find an answer. This is the code for the Input Box that I'm using InputBox("Enter Allocation For : " & .lstProddescp.List(i) & vbCrLf & "Current Qty is : " & MyQty) You will note "MyQty" this is what I want to make bold. It is a numeric value. In my limited knowledge I tried to format "MyQty" to bold, but only to fail. Any help would be appreciated. You cannot...

Recently used file list
Does anyone know if the size of the list can be increased from the default of 9? Thanks Tools/Options/General allows you to change the recently viewed size, but is pretty clear that the maximum is 9. >-----Original Message----- >Does anyone know if the size of the list can be increased >from the default of 9? > >Thanks >. > In xl2k and xl2002 (not sure about xl97), when you do a file|open, you'll see a History button in the left hand frame. Maybe you could use that to see more. Maria wrote: > > Does anyone know if the size of the list can be increased...

MS Exchange and Server 2003
Hey, At my company we run MS server 2003 and exchange 5.5. I want to send an email with an attachment to about 7500 users. we have IBM blade server with t1 connections. we have distribution list ready to go. What i want to know is that am i taking any chances in crashing the system. will this have any effect on the server. Thanks DJ ...

Sorting lists with references
How do a sort a list that contains references/formulas? (I want the reference to follow the row as it is "sorted around"..) Excel does't seem to want to sort a list containg formulas, it will only sort lists with numerical values.. Thanks! -- jonfu ------------------------------------------------------------------------ jonfu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28193 View this thread: http://www.excelforum.com/showthread.php?threadid=477281 janfu, If you want to sort based on the column of formulas, first copy the column with th...

Link Drop Down list with PIVOT Table in Excel 2003
Hi I'm using MS Excel 2003 and need a solution regarding link my drop down list with my pivot table. Query : I have created a drop down list in which month can be selected and wanted to link with pivot table in which month wise team count is displayed for a selected month. Presently I'm drag n drop the month in pivot table and its shows the count. But this exercise I have to do every time when I need the report. Is it possible that I have select the month from my drop down list (created in same sheet) and pivot table shows the data related to that month. Please h...

Validate if a mail user exsits
Can Exchange 2003 SP2 be configured to just drop any email that it receives for and @company.com address that doesn't have a valid username? I am looking for a built in option or relativley easy/inexpensive option, nothing like GFI Mailessentilas or the like, but preferably built in to Exch 2003. Thanks, Christopher Parrish Yes. ESM/Message Delivery/Properties/Recipient Filtering. Check the box "Filter recipients who are not in the Directory" Now you need to enable Recipient Filtering. ESM/servers/Servername/Protocols/SMTP/Default SMTP Virtual Server/Properties. On the...

distribution List Limit
HI all, i know this is not the right place where to post but i try. Do you know if there is a limit for number of contact in outlook's distribution lists ? How many address can i put in each one ? Outlook 2003 sp3 on windows 2003 sp3 exchange 2003 sp2 thanks ...

Custom Default Address Lists for multiple domains
We have two domains in one exchange site so we can share free/busy and address lists. These two domans are physically two different companies so we both need seperate default address lists that contain: users, groups, public folders and contacts. I've been messing around with filters for a few weeks and have not found any that work. Can anyone give any suggestions.... we'll say the domains are abc.com and xyz.com in a Big Company exchange site. abc.com and xyz.com are in seperate networks and seperate administrative groups. Thanks for any help! ...

Free Download: Visio Connector for MBSA
Visualize the security status of your network with the Visio Connector for MBSA. This free utility allows you to view the results of an MBSA (Microsoft Baseline Security Analyzer) scan in a clear, comprehensive Visio 2003 network diagram. You must have both Visio 2003 and MBSA 1.2.1 for this connector to function. Learn more or download this free utility at http://www.microsoft.com/technet/security/tools/mbsavisio.mspx Sanjay Puri This posting is provided "AS IS" with no warranties, and confers no rights. I have scanned my PC with MBSA v. 1.2.4013, importing the report ...