Data Validation List - Can I have multiple ranges displayed?

[Jim]

I am using Excel 2007.

I have two named ranges: Customer_ID and Customer_Name.

I currently have a data validation list with the source '=Customer_ID'.
This works fine to look up customer numbers or I can change the source to
'Customer_Name' and look up by name. At some times it's easier to lookup by
name, and others by number. Is it possible to have the drop down list
display both?

 

[Otto Moehrbach]

You would have to make a list of one column with the name and ID in each
cell. I don't think this would be useable because one of them would not be
sorted. Can you do with 2 DV cells, one for name and the other for ID and
let the user choose which one to use? HTH Otto

 

[Jim]

When someone chooses the Customer ID from the data validation list, a whole
group of VLOOKUP's activate lower in the sheet to look up that customers
records. The VLOOKUP's are seeing which record by the value of the Customer
ID cell. If I have another cell beside it for Customer Name, how would I set
it up so the VLOOKUP so it knows which to check? I imagine there is an easy
way to do in VBA, but I would prefer to avoid VBA is at all possible due to
security reasons.

 

[Roger Govier]

Hi Jim

Expanding upon Otto's suggestion.
You could have 2 alternate input cells, one with Customer Name and the
other with Customer ID, with appropriate DV dropdowns for each.

In a third cell (which could be hidden or "off screen", you could use If
formulae and Vlookup's to ensure that you had a Customer ID as the result.
Use this third cell as the source of your subsequent Vlookup's in the
remainder of your sheet.

 

[Jim]

Roger,

I understand the concept, however what formula would I use in the 'third'
cell to check the first two, whichever was the latest to be changed?

Thanks for helping out.