Autocomplete and INDIRECT

[antepia]

Hello everyone!

So, my issue goes like this:

In column A, cell A2 I have a dropdown with country names.

In column B, cell B2 I have a dropdown with city names that works with the INDIRECT function depending on the value in A2.

Now, what I want is to create another dropdown in column C, cell C2, which will have street names:
- I need this to be linked with the value in B2 (with INDIRECT again I guess), so that depending on the city selected only this city's streets are available, AND
- Since there are hundreds of streets I need the dropdown in C2 to work with autocomplete, so when the user starts entering AB for example only streets starting with "AB" appear.

Any suggestions?


PS. I have found a code here http://www.contextures.com/xlDataVal10.html but it seems it doesn't work with INDIRECT.

[ragulduy]

Hi - if you download the example workbook from that site you link, it has code like this:

Please Login or Register  to view this content.

which sets up the combobox, the value of the str variable is what the address for your fill range is, so if you change it to:

Please Login or Register  to view this content.

Then it will be linked to whatever address is in Range("A1").

I don't quite see how you are going to link it to the value in B2, as this will be a city name, presumably you will need some sort of lookup function to work out the address of street names for that city. You need to put this as the str variable name.

[antepia]

Hi ragulduy and thanks for the reply!

What exactly did you change? The two codes you posted look identical. Am I missing something?

[ragulduy]

Sorry, copying and pasting isn't one of my strong points. In what I was testing I changed:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[antepia]

Thanks ragulduy this worked for me!

[antepia]

Sorry, copying and pasting isn't one of my strong points. In what I was testing I changed:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hello again! How can I modify this if I want to have an autofill drop down list in every row, linking to the relevant cell?

For example:

The dropdown in cell C2 should be filled based on B2 (this alone works with str = Range("B2") as per ragulduy)
The dropdown in cell C3 should be filled based on B3
The dropdown in cell C4 should be filled based on B4
The dropdown in cell C5 should be filled based on B5

etc...

[antepia]

Sorry, copying and pasting isn't one of my strong points. In what I was testing I changed:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hello again! How can I modify this if I want to have an autofill drop down list in every row, linking to the relevant cell?

For example:

The dropdown in cell C2 should be filled based on B2 (this alone works with str = Range("B2") as per ragulduy)
The dropdown in cell C3 should be filled based on B3
The dropdown in cell C4 should be filled based on B4
The dropdown in cell C5 should be filled based on B5

etc...