Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.