**Question description:**

*This user has given permission to use the problem statement for this
blog.*

how to apply auto-populate on drop down list that is dependent -
ex: cell 1: either enter an agency or not - cell 2: depending the agency chosen it will show the linked clients - if no agency is selected, it show the direct client list -
how do I populate the address depending of the selection of cell 2

Solved by D. H. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user
information.

Excelchat Expert
28/03/2018 - 03:49

Hi

User
28/03/2018 - 03:49

hello, we use it as our tool

Excelchat Expert
28/03/2018 - 03:49

Welcome to got it pro

User
28/03/2018 - 03:49

have been on this issue for 2 days

User
28/03/2018 - 03:49

thank you!

Excelchat Expert
28/03/2018 - 03:49

can you paste sample data so that i can help

User
28/03/2018 - 03:50

of course! hold on

User
28/03/2018 - 03:52

sorry my validation didnt follow

Excelchat Expert
28/03/2018 - 03:52

can you explain more so that i can help you better

User
28/03/2018 - 03:52

sure

User
28/03/2018 - 03:53

in sheet 1 : agency should be a drop down list

User
28/03/2018 - 03:53

related to sheet 2 cell A1

User
28/03/2018 - 03:54

Nom: is the list of client related to agency

Excelchat Expert
28/03/2018 - 03:54

click on A3to A5 and format as table

Excelchat Expert
28/03/2018 - 03:55

do you get it?

Excelchat Expert
28/03/2018 - 03:55

this is the link for what you exactly want

User
28/03/2018 - 03:55

sorry the google excel is slightly different from excel app itself

Excelchat Expert
28/03/2018 - 03:55

http://www.contextures.com/xlDataVal02.html

Excelchat Expert
28/03/2018 - 03:55

have alook at this

Excelchat Expert
28/03/2018 - 03:56

you basically want to create dependent dropdowns

User
28/03/2018 - 03:56

but then how do I populate the address depending of the Nom cell

User
28/03/2018 - 03:56

Nom is dependent to Agency

Excelchat Expert
28/03/2018 - 03:56

have a look at the link i shared

Excelchat Expert
28/03/2018 - 03:57

just follow it step by step

User
28/03/2018 - 03:57

the depend drop down list i know how it works

User
28/03/2018 - 03:57

its the autopopulate

User
28/03/2018 - 03:58

with dependent dropdown list that I dont know how to do

Excelchat Expert
28/03/2018 - 03:59

yes that is what i have shared

Excelchat Expert
28/03/2018 - 04:00

refer create first dropdown

Excelchat Expert
28/03/2018 - 04:00

then refer create dependent dropdown

Excelchat Expert
28/03/2018 - 04:02

do you get it?

User
28/03/2018 - 04:02

just a moment please

Excelchat Expert
28/03/2018 - 04:02

sure

User
28/03/2018 - 04:02

just doing the dependent drop down for you :)

Excelchat Expert
28/03/2018 - 04:02

great

Excelchat Expert
28/03/2018 - 04:03

since it is step by step process hence asking you to refer as it is better graphically represented

User
28/03/2018 - 04:04

i'm actually having a hard time working on this google sheet

User
28/03/2018 - 04:04

it's not the same

Excelchat Expert
28/03/2018 - 04:04

work on your excel

Excelchat Expert
28/03/2018 - 04:04

not on this spreadsheet

User
28/03/2018 - 04:04

ok

User
28/03/2018 - 04:05

I laready have it on my excel

Excelchat Expert
28/03/2018 - 04:05

this might not support all the features of excel

User
28/03/2018 - 04:05

it is done already

Excelchat Expert
28/03/2018 - 04:05

yes that is better

Excelchat Expert
28/03/2018 - 04:05

great

User
28/03/2018 - 04:05

so that part was done 2 days ago

User
28/03/2018 - 04:05

but is stuck on how to autopopulate

User
28/03/2018 - 04:05

i've tried and it doesnt work

Excelchat Expert
28/03/2018 - 04:06

when you say autopopulate what do you exactly want?

Excelchat Expert
28/03/2018 - 04:06

you can refer to more balnk rows when you are feeding values in them

User
28/03/2018 - 04:06

well if i select NOM it should automaticalky show me the address from the client i have selected

Excelchat Expert
28/03/2018 - 04:06

so when you fill some value it will get autopolated

User
28/03/2018 - 04:07

so step 1: user either select an agency or not

User
28/03/2018 - 04:07

step: 2 choose the list of client dependent to the agency or the list of if there is no agency

User
28/03/2018 - 04:07

step 3: adresse of the selected step 2 should appeard

User
28/03/2018 - 04:07

appear*

Excelchat Expert
28/03/2018 - 04:08

so there is an option to create Third Dependent List in the link

Excelchat Expert
28/03/2018 - 04:08

did you see that?

Excelchat Expert
28/03/2018 - 04:08

we will need to connect all of them

Excelchat Expert
28/03/2018 - 04:09

are you getting ?

User
28/03/2018 - 04:09

yah i see it

Excelchat Expert
28/03/2018 - 04:09

when you connect them all then all your dropdown will be in sync

Excelchat Expert
28/03/2018 - 04:10

and that is what is required

User
28/03/2018 - 04:10

but will the address show without the user having to select it?

User
28/03/2018 - 04:10

I have put the formula in the sheet - can you see?

Excelchat Expert
28/03/2018 - 04:11

which formula?

Excelchat Expert
28/03/2018 - 04:11

yes address should populate without you selecting it

Excelchat Expert
28/03/2018 - 04:11

that is the basic idea

User
28/03/2018 - 04:11

do you see in red

Excelchat Expert
28/03/2018 - 04:11

2nd dropdown is dependent on first

User
28/03/2018 - 04:11

B4

Excelchat Expert
28/03/2018 - 04:11

and third is dependent on first and secong

User
28/03/2018 - 04:12

that is the used formula at the moment

Excelchat Expert
28/03/2018 - 04:12

yes i see

User
28/03/2018 - 04:12

so Nom is either dependent of Agency or if it's empty it gets another list

User
28/03/2018 - 04:13

so address should be able to get from the list of agency or from the "other" list from empty

Excelchat Expert
28/03/2018 - 04:13

so that you can do when you create dependencies

User
28/03/2018 - 04:13

so substitute should be the key right?

User
28/03/2018 - 04:13

=INDIRECT(SUBSTITUTE(B2&C2," ",""))

Excelchat Expert
28/03/2018 - 04:13

yes

User
28/03/2018 - 04:14

okie!

Excelchat Expert
28/03/2018 - 04:14

that is what is mentioned in the link

Excelchat Expert
28/03/2018 - 04:14

that is what i am saying ..it is in the link

Excelchat Expert
28/03/2018 - 04:14

you need to follow the steps

Excelchat Expert
28/03/2018 - 04:14

you are going in right direction my friend

User
28/03/2018 - 04:14

ok

Excelchat Expert
28/03/2018 - 04:16

please do give good rating when the session ends

Excelchat Expert
28/03/2018 - 04:16

it really helps

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user
information.*