Fast Solution to Problem: Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field

Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field

Greetings --
I've got a situation where Access data needs to be output into Excel, emailed out, updated, and brought back into Access.  To maintain data integrity (folks can't spell around here), I plan to have a workbook tab of lookup lists and use Excel's validation
to create pick lists.
Working in Access, I routinely have multi-column combo boxes where the prime key for the lookup values is in the first column, but hidden, and the "people friendly" text is to the right.  I'd love to do something similar in Excel with data validation.  The
method in my madness here is that I could then match on the PK value in the return data, instead of having to match on text.
Is it possible to build a data validation in Excel that emulates the "multi-column combo box with the key field hidden" like Access supports?
Thanks, Mark

Keys to the Problem Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field

Download Error Fixer (Free)

Here's a slightly quirky method that has a few advantages.
Put a list in some cells, lets say A1 to B4 for an easy example.
Have the Headings in Row1, so that A1 = 'PK' and B1 = 'MyValue'.
Fill the other rows with some meaningful values.
Then select one cell anywhere in the list and Insert -> Table.
Click Ok.
The list is converted to an Excel Table.
Go to Table Tools and rename the table to something that makes sense to you, say 'AccessList001'
Now use Formulas -> Name Manager -> New to create a named range, call it FriendlyText and set the Refers To box to =AccessList001[MyValue]
Create a data validation list in a cell, say cell E2.
The source should be =FriendlyText.
You now have a cell with a drop-down that selects a value from the MyValue column in the data table.
In an adjacient cell enter =INDEX(AccessList001[PK],MATCH(E2,AccessList001[MyValue],0)).
If you have followed all that, you now have a drop-down in cell E2 and the corresponding primary Key in a cell nearby.
The advantages of using data tables are that all range are dynamic.
Add new data and everything still works.
Rename the table and everything continues to work.
Rename a table column and you still won't break it (the formulas are changed for you).

Ed Ferrero
www.edferrero.com

System Restore - Fix All Errors including Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field

• Click on the Start button on the Taskbar.
• Type "System Restore" (without quotes) in the Search box and hit Enter or,
• Click on System Restore (when you see System Restore pop up  in the list of search results).
• Make sure the "Recommended restore" radio button is checked on the restore utility window.
• Click on Next and follow the instructions given.

Note: There is the option on the restore utility to select "Choose a different restore point". Unless you have a specific reason to select a different date and time than what Windows recommends, just select the recommended option stated in the steps above. If you choose to select another one, follow these steps:

Click on that desired date and time, and Windows 7 will perform a scan for any affected programs in that restore point.

Once completed, click Next and follow the instructions given.

Once you have selected the restore point, your system will start the restoring process, after which you will have to restart the system.

Recommended Method to Fix the Problem: Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field:

How to Fix Adding lookup lists to Excel workbook for eventual re-import into Access -- showing the text, but hiding the Access autonumber field with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.

Related: How to Fix - 64g ssd with a 500g regular drive?,Allow Unhide Rows in Protected Workbook [Solved],[Solved] Get in Excel 2007 data from Access 2007 out of self-built Queries,[Solution] How can I temporarily disable 'service manager' to install Adobe flashplayer?,[Anwsered] When I try to watch a flash video, I am told occasionally that I don't have Adobe Flash.,Solution to Error: Black screen during boot sequence,[Solved] Can't restore Windows 7 64-bit from external hard drive,How to Fix - IE 11 Enhance Protect Mode reset issue with add-on's?,Solution to Error: Internet Explorer 9 update/install error - Error Code 80092004,Upgrading to IE 8 causes cookies to get deleted when starting IE [Anwsered],Solution to Problem: All programs try to start from windows component
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,How to Fix Error - Getting an error "not connected to the internet" while trying to install Samsung Kies?
,How to Fix - Internet Explorer shuts down and reopens tab when attaching to email or uploading files.?
,Fast Solution to Problem: Sending Error Message
,[Anwsered] Thinkpad 8611 Boot,How to Resolve - Svchost Helper?,Fast Solution to Problem: L30 101 Driver Windows 7,Troubleshooter of Error: Io Device,How to Fix Error - Dell Laptop Code 39?
Read More: Adding saved shortcuts to internet explorer from documents folder [Solved],Solution to Error: after booting up from either sleepmode or hibernate, if I do nothing it shuts down. How can I change the length of time before it does this?,[Anwsered] After installing 4 additional GB or ram, Windows tells me I only have the original 2GB. Why?,How to Fix - adobe flash says installed but isn,t. windows 7 and 64bit cpu?,[Solution] Adobe Illustrator CS4 is running very slowly for Windows 7 64-bit computers,a file called mDNSResponse.exe. is causing bonjour not to operate properly,what should I do?,A QUESTION USING THE "IF'S" Formula.,A continuos flashing window with which title is C:Windows\System32\cmd.exe, and has the following message: The syntax of the command is incorrect.,Acrobat compatibility issue and you tube problems____,ActiveX on IE 9 not loaded