Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drop down on vlookup tab

Status
Not open for further replies.

Ofina

Technical User
Feb 25, 2004
156
US
I have a file that has a tab full of data, and a tab where you can enter a bunch of cells and it automatically pulls, via vlookup, from the data tab the corresponding information. It's customers and their item codes.

Sometimes it pulls the wrong customer's information if they happen to have the same item code as the one I want. I'd like to add at the top a drop down where I can select the customer whose item codes I want to search for. How can I do that?
 
hi,
if they happen to have the same item code
Then you have a problem that cannot be solved with formulas and logic since it it ILLOGICAL that A == B and A == C and B <> C.

FIX YOUR DATA!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
I don't understand what you are saying. I understand that your formulas don't logically make sense. But, I don't see how that applies to my situation.

We have 2 customers who are associated with each other, so they have the same item codes. I would just like to be able to select which customer to search for.
 

Please post a sample of your table that illustrates this issue, like...
Code:
Dept_ID  Emp_ID    Emp_Name
ABC211   20050011  T Wiggins
ABC211   20040717  B Cavendish
ABC211   20058598  J Evans
for a structural example

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
How do I put it in looking like a table like that?
 
paste in your table example

SELECT the entire table

Click on the CODE icon just above this window.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Code:
customer  our item   customer code
1000	  123	     999
1000	  456	     888
1002	  123	     999
 
okay, based on this example, what do you need to do. Please be very specific.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Sorry, I thought I was.
On my lookup tab, I want to have a drop down box containing a list of customers on the data tab, where I can select, in my example, to search customer item code 999 but only for customer 1002. As it is the vlookup gives me the first instance of the customer item code and would therefore return all the data for customer 1000, instead of the 1002 that I actually want.
 
What is [highlight]customer item code[/highlight]???

I do not see [highlight]that[/highlight] in your example!!!

I see [highlight]customer code[/highlight] and [highlight]our item[/highlight]???

You must be VERY SPECIFIC! We cannot see what you are thinking. We can only see what you "say" here, and what you have said is not consistent.

I suppose that I could ASSUME that you meant customer code, but should I have to make assumptions about your intent?

So assuming that you want customer 1002 and customer code 999, then what?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Sorry. Customer is the customer, customer code is the customer item code, our item is our item code.

Alright, the file already has a data tab that looks like my sample, with other data as well that is not relevant to my problem. The lookup tab already allows us to enter in the customer item code (or customer code in my sample table) and it looks for that code in the data table and returns the customer and our item code (our item).

So, as an example, if I enter in 999 in my lookup tab, it will return customer 1000 and our item 123. But, I want to know about customer 1002, not 1000. So, I want to add a drop down to my lookup tab where I can select 1002 forcing it to return the data I want to see and not the data for the other customer 1000.

Hopefully that explains it all.
 


you will have to use either MS Query or a PivotTable.

The latter would be the easiliest, using either customer or customer code in the Report Filter.

MS Query would be a bit more difficult, but could be more versatile and might need soe VBA code.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Seriously? I can't add a drop down containing data from another tab?
 
Sure you can using MS Query. Are you ready for a somewhat complex process to set it up?

Here's what would need to happen.
[tt]
1. You must have a unique list of either customer, customer code or both lists if you want to be able to seach either way. This list will be the source of your FIRST Data > Validation --LIST control.

2. Based on a selection in your FIRST DV control, MS Query will run, producing a list of the OTHER, ie if you selected a customer then a list of customer codes for the selected customer of vis versa. This list will be the source of your SECOND Data > Validation --LIST control.

3. Based on BOTH your selections another query will run to return the corresponding our item value.
[/tt]
Ready for that?


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Well, no. Since I was surprised that there seemed to not be a way to do what I wanted, I looked harder for it myself.

I have added a column to my data tab that concatenates the customer and the customer code. I added a drop down to my lookup tab that presents the data from the customer column of the data tab. Then changed my vlookup to pull based on the customer code the user enters concatenated with the customer they choose in the drop down.

And Bob's your uncle.

My only problem is that the drop down is not presenting with distinct values from the customer column of the data tab, but rather all of them.
 
Well isn't that what a drop down does -- gives you a LIST to select from?

Why not activate the Auto Filter in table and just make 2 selections?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Of course that's what a drop down does. That's what I wanted.

I made a dynamic column of sorted, distinct customers and now my drop down pulls from that.

I think I have exactly what I wanted now.

Thanks for your help though.

I'm not entirely sure that I understood everything that you suggested anyway. I can't make 2 selections because we're not just saying I have a customer code and I want to know our code; there are other much-easier ways of doing that. We have a list of customer codes that we have RFQs on and we copy the whole list and plunk it into one of the columns in the lookup tab and each row returns the information pertaining to that item.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top