Data Validation in 2007

Is there a way to make the Data Validation dropdown list dispaly 
descriptions but when you select one it puts in the corrosponding ID 
instead?

Example:

Name:    Phone #
Smith        360-482-4747
Bowers     360-482-7878
Gates        360-482-8877

So when I'm in the cell and hit the drop down and Select "Bowers" - it 
actually saves the phone number "360-482-7878" instead of "Bowers" 


0
Joe
2/26/2009 4:23:16 PM
excel 39879 articles. 2 followers. Follow

2 Replies
546 Views

Similar Articles

[PageSpeed] 34

Not directly but in an adjacent cell you could use VLOOKUP to pickup the 
phone-number
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Joe H" <hortoristic at gmail> wrote in message 
news:uCJKI6CmJHA.5028@TK2MSFTNGP04.phx.gbl...
> Is there a way to make the Data Validation dropdown list dispaly 
> descriptions but when you select one it puts in the corrosponding ID 
> instead?
>
> Example:
>
> Name:    Phone #
> Smith        360-482-4747
> Bowers     360-482-7878
> Gates        360-482-8877
>
> So when I'm in the cell and hit the drop down and Select "Bowers" - it 
> actually saves the phone number "360-482-7878" instead of "Bowers"
> 


0
bliengme (657)
2/26/2009 5:28:39 PM
Yes, you can. If your dropdown is in G2, for example, and you have the 
entire table o4 values n M2:N5, for eaxmple, you can right-click the sheet 
tab, select View Code, and put this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$2" Then
        Application.EnableEvents = False
        Target.Value = Application.VLookup(Target, Range("M2:N5"), 2, False)
        Application.EnableEvents = True
    End If
End Sub
Now, when you select Bowers, you'll see 360-482-7878!

Bob Umlas
Excel MVP

"Joe H" <hortoristic at gmail> wrote in message 
news:uCJKI6CmJHA.5028@TK2MSFTNGP04.phx.gbl...
> Is there a way to make the Data Validation dropdown list dispaly 
> descriptions but when you select one it puts in the corrosponding ID 
> instead?
>
> Example:
>
> Name:    Phone #
> Smith        360-482-4747
> Bowers     360-482-7878
> Gates        360-482-8877
>
> So when I'm in the cell and hit the drop down and Select "Bowers" - it 
> actually saves the phone number "360-482-7878" instead of "Bowers"
> 


0
rumlas (268)
2/26/2009 5:51:11 PM
Reply:

Similar Artilces:

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

SharePoint (WSS) 2007 / Outlook 2007 - Alert
This is a multi-part message in MIME format. ------=_NextPart_000_00AD_01C70E47.83B45950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have setup a SharePoint Server (WSS) 2007 and are using the alerting = functionality. After we changes stuff on WSS, an alert message is generated and sent to = an email account. This e-mail account is hosted on an Exchange 2003 server. When using Outlook 2007 to open the e-mail, we get: Cannot open this item. Wenn using OWA the e-mail can be opene fine. Does somehow have an explanation for th...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Adding extra data options
Is there a way to customize CRM to allow for adding another heading? I would like to add a second field similar to topic and would like to call it type. Can you add extra data fileds and types in CRM 3.0? You can add extra data fields to an entity. Go to entities customization at setting area. -- Marco Amoedo Plain Concepts http://geeks.ms/blogs/marco/ "xxdcmast" escribió: > Is there a way to customize CRM to allow for adding another heading? I would > like to add a second field similar to topic and would like to call it type. > > Can you add extra data ...

Chart printing issue in Excel 2007
A spreadsheet with charts was created using Excel 2003. I have Excel 2007 and saved it in compatibility mode. I inserted a couple colored lines on the chart and created my own legend based on these. A couple of issues: 1. When I close the file or even minimize, 2 of the colored lines on a couple of my legends disappear upon reopening. 2. When I try to print a chart, it looks good in Print Preview, but then looks magnified,half off the page, and only one of my drawn lines is printed. When someone with 2003 prints, the sizing is correct, but all of the colored drawn lines are missing...

Install 2003 after 2007?
I had Office 2003 and upgraded to 2007, other than Outlook I didn't like it. I wanted to reinstall 2003 Excel & Word. Per instructions here I uninstalled 2007 except Outlook and upon attempting to reinstall 2003 I get the error message, "Business Contact Manager requires Outlook 2003, please install Outlook 2003 before running setup". Help please. Thanks in advance... Bob Answered in the other group post "Bob Newman" <bobnewman@cox.net> wrote in message news:jH5Xl.21061$IP7.4196@newsfe23.iad... >I had Office 2003 and upgraded to 2007, other tha...

Re: Outlook 2007
I sent this 3/4 days ago but can not see it in the group. In addition after this each time I go into news groups the name of the group turns red in color?? Can not figure what this change in color means. > Each time I start Outlook jumps a message: "A data file did not close > properly > last time it was used. It will be checked.... bla, bla, bla..." > Vista ultimate, Dell computer. Could one do something?? Regards Ted > > A minute later comes up an other message that the file was checked. > Red means a subject you're following has been resp...

MS Access 2007, Forms
When using the "Forms Wizard", I need to use the "Style: Ricepaper", but it isn't available in the 2007 version and I have not been successful trying to find it in Office Online or on the web. Does anyone know where I can get it? Access 2007 has 25 AutoFormats, but as you noticed, they are virtually all new ones. It's actually nice to see that these were updated. To get the older Ricepaper style you could probably create a form in an earlier version of Access and then import it into your current database. If you're using the ACCDB file format, you would...

Tables in 2007
What are the advantages to converting a list to a table in 2007? Why does the table have a name? I did read in help that you can post a 2007 table to sharpoint services. What does that mean? -- Thanks. Confused Hi, Go to the excel help and type Tables then open the one that says Demo: Organize your data by using an Excel table, and then in How to do it, click on Overview of Excel Tables if this helps please click yes, thanks "Confused" wrote: > What are the advantages to converting a list to a table in 2007? Why does > the table have a name? > > I did read...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

Required OS for Exchange 2007
I have installed Windows 2003 R2 Enterprise 32 bit x64. When I try to run Exchange 2007, I receive "the image file setup.exe is valid but is for a machine type other than the current machine". Any idea? On Fri, 2 Mar 2007 22:52:43 -0800, "Xeon" <Xeon@donotemailme.com> wrote: >I have installed Windows 2003 R2 Enterprise 32 bit x64. When I try to run >Exchange 2007, I receive "the image file setup.exe is valid but is for a >machine type other than the current machine". Any idea? > So you arent using the 64 bit version of Windows? No...

Outlook 2007 missing emails until restart
I have a user running Vista Business Basic along with Office 2007 Professional that is having a problem with emails not showing up in the inbox. The user has a PDA that does get the email and if you search of the email on the computer, you find it and can open it without issue. However, the email still may not show up in the inbox until a restart of Outlook. Currently the user has caching enabled and other then the missing emails, runs stable. However, if we turn off the caching we experience an application crash with the following: Event Name: APPCRASH Applicatio...

Does table size make a difference in how well Access protects data
I am having a problem that has been going on for several weeks now. I am slowly trying to figure it out. the problem is that Access 2003 keeps deleting records after they are entered. Quick view of database: running Access 2k3 back end and frontends, 12 machines access the DB at any one time, mixture of Win2k Pro and Win XP pro all patches and service packs installed for all OS's as well as Access 2k3. Running Client/server set up controlled by Small Business Server 2k3. database resides on a machine running XP Pro. when new orders are entered into the system, the DB will tell the d...

Sending data with CAsyncSocket.
Please tell me if/where I am wrong... If I have an established TCP connection using CAsyncSocket... When I send data I will normally be sending a packet that is about 100 bytes or so. I would send each packet with a separate call to "Send". Normally the Send will return a value equal to the number of bytes I sent. I would just write my code to call Send whenever I need to send a packet. Normally I would never receive an error or an indication that fewer bytes were sent. But if I did, I would handle this error. There is no need for me to use the "OnSend" function. Co...

PF-replication: SQL-Data -> PF-contacts
Hi, we have some customer information stored in our MS-SQL Server 2000 and like to sync it with the contacts in our public folder (Exchange 2003). Of course ye can do it manually, but is there a way to automate it? Or is it possible to make a public folder with all data stored on the sql-server? (kind of a view) Kind regards, Paul ...

SRS Connector Validation error
When I try to install the SRS Connector for CRM 4.0 I get an error that says: Check ReportServerValidator : Failure: Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine. Things I've tried: Putting the server name as well localhost in the configuration database field Using the install-config.xml to explicity name the reporting server and url I am attempting to install the connector on a known good SRS install. The address is http://localhost/reportserver which is the default. I'm all out o...

Charts: 2007 to 2003
Have been doing financial presentations with lots of line graphs, bar and column charts, pies, scatter plots, etc. in PowerPoint 2007, which creates an Excel file for each chart. I need to convert them for use in 2003 as not everyone has 2007. The problem is, when the charts are edited in 2003 colours change and I don't even have the option to modify the default colours in the embedded Excel workbook. Any ideas as to how to solve this problem? Many thanks... ...