Excel 2010 dropdown list displaying id & description that populates only the id

Hopefully this is simpler than it appears and I'm searching for the answers improperly because I haven't been able to locate them....

I am creating an Excel 2010 Template with multiple dependent lists.  I want to be able to display the Company number and the Company name in the dropdown list and when the user chooses the appropriate Company, the cell populates with only the Company number.

I prefer to stick with the data validation rather than switching to form or ActiveX controls.
LVL 4
urthrilledAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
Do you have a sample workbook available?

The way you are describing the current scenario, I don't think it will be possible.

If you were willing to add an additional column, you could use the data validation cell to pick a Company Name, and the new cell would provide the Company Number based on a VLOOPKUP formula.
0
Martin LissOlder than dirtCommented:
What does the range where you get the validation data look like?
0
Steven HarrisPresidentCommented:
I am assuming the OP is using a standard 2 column table with Company Name in one, Company Number in the other.  Then again, you know what they say - "When you assume..."

I have attached an example of what I offered above:

Add one additional Column and use a VLOOKUP to populate the Company Number, based on selection of a Company Name through a dropdown box.
VLOOKUP.xlsx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

urthrilledAuthor Commented:
So the sample ThinkSpaceSolutions posted is the only answer I've found and it's not ideal, as the Company and Company ID columns are dynamic and I did test and the lookup will accept a column range (rather than absolute).  But I need to show the Company ID and Name and when they choose, the only data that is posted is the ID, so with this I have created a view that combines the ID and Name and a separate ID column for the import so I can use the combination for the list and the ID column for the vlookup....

I was hoping someone out there knew something I wasn't finding.
0
Martin LissOlder than dirtCommented:
If you were to answer my "What does the range where you get the validation data look like?" question I might be able to help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven HarrisPresidentCommented:
MartinLiss is absolutely correct.  If we know exactly what you are dealing with, we can be of further assistance.  The idea I presented was based on a best-guess since there was no actual data provided.

Excel is pretty flexible as long as you are not trying to run too many formulas based in or out of a single cell, which I believe you are trying to do.  All that will do is run into a circular reference error.

If you don't mind having that extra column like I suggested, use a CONCATENATE formula to combine Company Name and Company Number into a string, where you pull your list validation from, then adjust your VLOOKUP accordingly.

Otherwise:
I prefer to stick with the data validation rather than switching to form or ActiveX controls.
Are you opposed to using a macro?
0
urthrilledAuthor Commented:
This is an Excel template that I am preparing for individuals Credit Card chares.  I have several scenarios for dependent lookups

1 is

Field 1      Field 2
1                Company1Name
2                Company2Name
3                Company3Name

Depending on the value of Field 1, a unique to the Company list of GL Accounts is provided

Field 1                   Field 2
1000000-00          GLAccountDescription1
2000000-00          GLAccountDescription2
3000000-00          GLAccountDescription3

Then, based on the type of Charge it is, if it's Job related, the Job Number and Job Name will need to be listed, based on the Company Number, then based on the Job number chosen, a unique to that Job list of Phase Codes is listed... etc., etc., etc....

Is that enough to go on or?

Thank you,
Doris
0
urthrilledAuthor Commented:
I do understand using a concatenated list and then the vLookup formula in another cell to get the appropriate data, just asking is there's a more concise method.

Macros are welcomed, I just don't want to go with ActiveX and/or Form Controls (and maybe that's because I don't understand them), but I have to do calculations on the columns of the  rows entered and if part of that is drawn-in controls, then it muddies up how to get formulas into the worksheet
0
urthrilledAuthor Commented:
Thank you for your input!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.