If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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? . |
#5
|
|||
|
|||
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? . . |
Thread Tools | |
Display Modes | |
|
|