Data Validation List - Can I have multiple ranges displayed?
 

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?

Data Validation List - Can I have multiple ranges displayed?
 

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" wrote in message
...
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?

Data Validation List - Can I have multiple ranges displayed?
 

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.

"Otto Moehrbach" wrote:

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" wrote in message
...
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?

.

Data Validation List - Can I have multiple ranges displayed?
 

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.
--
Regards
Roger Govier

Jim wrote:
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.

"Otto Moehrbach" wrote:

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" wrote in message
...
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?

.

Data Validation List - Can I have multiple ranges displayed?
 

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.

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

Jim wrote:
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.

"Otto Moehrbach" wrote:

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" wrote in message
...
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?

.

.