Vlookup to generate Drop Down list results

Is it possible to use a Vlookup to generate a drop down list?

The list is rather substantial and the overall excel workbook is 8Mb so I can't post it.

The logic behind the statement would be:

If L8="this product", Lookup on I8 (Which is a location code) then look at the location codes range is A1:A40,000 and return the providers at that specific location. So in the location, A1 and A2 could be the same location code, and B1 and B2 are different providers. There might not always be a second option for provider.

Thoughts?

I could put up a mock list of the location/providers if that helps

Here we go.

User will populate phone number into Column A, in this example A2, B2 removes periods, dashes, and spaces and returns first 6 digits (NPA/NXX). C2 does a Vlookup based on B2 to return a CLLI. User selects access type in D2. We need E2 to look at D2, if D2="ADSL", look to Sheet2!B2:C23, and create a drop down list of possible providers.

For your reference, I have color coded the 3 instances on Sheet2 where there is more than one provider per CLLI.

Thoughts?

Hi

What is the relationship between ADSL and the 3 items highlighted? There doesn't seem to be any connection that I can see.

rylo

the data found on Sheet2 is a list off all ADSL providers (C3) in that respected CLLI (C2). Their existinances in that list is the actual relationship. Only DSL providers are in the list.

So how are you doing this? Find the provider from column C that matches the CLLI from column 2, then finding all the column 2 entries that are for the same provider found in column C?

rylo

Currently its not being done, if user picks ADSL as access the the carrier list returns all possible carrier providers. the attempt was to have the carrier list returned based in the CLLI, thus limiting to only the provider present in that CLLI.

Not sure how to do this cleanly, so I have come here.

OK, didn't make my question clear.

In your example file, you have highlighted 3 items to be brought back. How did you make those selections? What business rules did you apply to get them?

rylo

Sorry, to be clear, I highlinghted the 3 examples to show you that is where there are 2 providers in that CLLI. My intent is not to have those 3 returned, rather if one of those 3 CLLI's is determined by the correct NPA/NXX, it would generate a drop down list with 2 providers, otherwise the only option would be the single provider like the other CLLI's included.

That better?

Hi

OK, select sheet2 then
1) D2: =IF(B2=Sheet1!$C$2,ROW(),"") - copy down as required
2) F1: =INDEX(C:C,SMALL(D:D,ROW())) - copy down as required
3) Formulas, name Manager, New, Name: Carrier, Refers To: =OFFSET(Sheet2!$F$1,0,0,COUNT(Sheet2!$D:$D),1) and close it out.

Now select Sheet1
E2: Data, Data Validation, List, Source: =Carrier, OK.

This should give you a drop down in E2 with 2 entries: AMT and Covad.

HTH

rylo

This worked well, thanks!

but can I make this apply to Sheet1!E2 based on the condition of Sheet1!D2?

ie if Sheet1!D2=ADSL, run this rule, otherwise if Sheet1!D2=Cable, return a set drop down list of 5 carriers

Hi

Ok, on sheet2 enter your 5 cable carriers in the range G1:G5. Highlight that range, and give it the name Cable. Follow the steps 1-3 from post #10, but in step3, make the name ADSL not Carrier.

While on sheet2, add a new name called Carrier with refers to: =IF(Sheet1!$D$2="Cable",cable,ADSL)

Now go to sheet1, and in E2, enter the step from post #10.

So now if you select CABLE in sheet1!D2, you should get your 5 nominated cable carriers, but if you select anything else, then you will get the ADSL carriers.

HTH

rylo

Ok, I did get it to work, but how would I continue this down? See example book attached to this post.

The formula you built works on a single row, but I need this to support multiple rows of queries.

HI

OK, go another way and have a fixed series of named ranges with the same name as the option in column D.

Then the validation list formula in column E can be =indirect(d2) (for cell E2).

rylo

You might need to go speak slower and use smaller words. Lost me on that one. Please

What I'm suggesting is that you have a series of fixed named ranges, containing the options you require, and make sure they have the same names as the options from the Drop down.

Then when you select a drop down, it will select the required named range.

If you still need to have one of the named ranges that is restricted by the number prefix, then make it dynamic in the way that I've suggested.

rylo

If I am understanding you correctly, the task you are suggesting would become massive in nature. The sample data on Sheet2 is only 22 Rows, but in reality that data set is 71,000 rows.

What if this Sheet2 looked like this
A B C D
1 360615 AFACCOMA Qwest
2 360616 AFTNMNAF Qwest
3 360617 AFTNWYMA Qwest
4 360618 AGDLCA11 PacBell Covad
5 360621 AGFIAZSR Qwest Covad

I can modify the data set so that the second CLLI (if applicable) is in its own column.

C3 on Sheet1 was where the 8 digit code was populated

Any way to do a drop down data validation with a Vloopup('Sheet1'C3,'Sheet2'B1:D5, Return Column C & D for that row where C3 is found) ?

Drop down would still work if there was a blank in the second position, right?

The problem I get is what happens when I get to site 2 (row3 on Sheet1)

Hi

Looking back through the posts, I get the impression that you want a mix of fixed providers for some access types, but restricted providers for other types based on the phone number prefix. In your access drop downs, you have 5 options: T1, ADSL, Resold ADSL, CABLE and ETHERNET.

Which of those 5 options have fixed provider(s), and which have restricted?

For the fixed ones, then build a defined, fixed named range.

On thinking about this further, I think you will have to do a code solution that will tailor the available options for every line. It would have to be an event code that will action when there is a change to a row.

Can you either update the example in #14, or put up a new example, with more options, and explain exactly what should be available for every example.

rylo

Hi,

I can't seem to figure out how to generate a drop down list with V lookup.

My situation is like this.:

In sheet1 I have three colums: Projectcode, Project, Location
The projectcodes generally correspond with only 1 project and 1 location, so I've created an array in sheet2 with the projectcodes and their corresponding projects and locations.
Like this, I can enter a projectcode (say in A1) in sheet1 and with VLOOKUP the corresponding project (in B1) and location (in C1) are automatically entered. This works perfectly.

Now here comes the problem, there are a few projectcodes (5 out of 25) that actually cover 2 or more locations. So instead of 1 value in sheet1 C1, I would like a dropdown list in C1, based on the array I made in Sheet2.

In sheet2 I made the following array:
The first column lists all the projectcodes
The second column lists the projects
The third column is a list of single values for location or dropdown lists of locations based on column 4-15. I have created the dropdown lists manually with data validation - list - range:column 4-15
Colums 4-15 represent all the possible locations, when a project code corresponds with the location (of one particular column), the value in that column is the name of the location. If the project code does not correspond with the location the cell is left empty.

Since I am only using 25 projectcodes, it is not a problem to create the dropdown list manually in sheet2.
But in Sheet1, I will likely need over a thousand entries, so I need a uniform formula that returns single values for a location (20 out of 25 cases so), but also dropdown lists if needed (those 5 out of 25).

Sorry to not have an example posted, I hope it is clear anyway.
Can anyone guide me?