urthrilled
asked on
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.
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.
What does the range where you get the validation data look like?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I was hoping someone out there knew something I wasn't finding.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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?
ASKER
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
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
ASKER
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
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
ASKER
Thank you for your input!
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.