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.