Drop Lists - Dependant and returning Unique values only

Hello,

I am sure this question is asked many times in many different ways:

I hava sheet with a table similar to:

COMPANY	CITY
COMPANY-01	CITY-A
COMPANY-01	CITY-B
COMPANY-02	CITY-B
COMPANY-03	CITY-C
COMPANY-04	CITY-D
COMPANY-05	CITY-E
COMPANY-06	CITY-E
COMPANY-07	CITY-E
COMPANY-07	CITY-F

ECT...

I would like to make an easy to use form to fill out Company Address's based on Company Selected and if more than one City, City selected per drop down list.

From what I understand using Tables will allow the data selection to grow/expand as more companies are added to the table.

I have attached some sample data also to help illustrate what I am trying to do.

I have tried using INDEX MATCH to return Cities matching Company but only the first City match to company appears in the drop down list.


thank you!

Mars

It seems I still have the same problem I came up against once before trying this.

The drop down list for the Company selection returns only one selection in the xlsx file you attached for download, I have to spend some serious time to figure out why.

I'll keep playing with this, hopefully learning a thing or two.

Cheers!

Mars

Here's another way.

Wow, thank you!

This is pretty much the cheese.

I certainly will need to study this one, to understand it fully, but does the trick for a static list of Companies/Addresses.

I remember being able to update the unique list dynamically as a user inserted more company info to the parent table using COUNTIF INDEX/MATCH,

Thanks A heaps Both of you,

I thought Spreadsheets would be a cinch compared to database, but I was wrong.

Cheers!

Mars

@marsofearth

You are welcome. Glad it does the job.

Nice!

Typo fix has it working like a Champ!

This is great.

Thank You!

Mars