Log In
Register
Home
December 7, 2020
Offline
Hi
I am trying to create dependant drop down. Here is the information...what am I doing wrong Arrggh!
Dependant drop down 1
Named Range is "Facilities"
Non-Well/Facility/Camp or Multi-Well/Facility/Camp Codes
Well, Facility & Camp Codes
Dependant drop down 2
Named Range is "NonMulti"
Non-Well/Facility/Camp or Multi-Well/Facility/Camp Codes (this is the title)
CPB - Cooper Basin
CISK - Chemical Injection Skid
OPSK - OverPressure Skid
Dependant drop down 2
Named Range is "FacilityCodes"
Well, Facility & Camp Codes (this is the title)
1000 - Beach Standards
1002 - Moomba
5500 - Kiana Oil Field
5547 - Christies
6115 - Sellicks
ALD - Aldinga
ARN - Arno
BAL - Bales
BAN - Balgowan
BAU - Bauer
BEH - Beharra
BOS - Boston
BUT - Butlers
CAL - Callawonga
CHI - Chiton
CKS - Congony/Kalladeina/Sceale
DAS - Dashwood
DAV - Davenport
DRI - Driftwood
ELL - Elliston
ENC - Encounter
GER - Germein
GLE - Glenaire
GRO - Growler
HAB - Habanero
HAL - Halifax
HAN - Hanson
HOL - Holdfast
JACK - Jackson
K - Katnook
KAN - Kangaroo
KEP - Keppel
KIA - Kiana
KL - Katnook & Ladbroke Grove
LYC - Lycium
MAR - Marble
MOO - Moonta
MPL - Moomba Pipeline
MPL - Moomba Pipeline
NEP - Nepean
OSM - Osmanli Well - Area CKS
P07INP40 - Flax
P106 - Middleton
P106 - Canunda
P106 - Udacha
P106 - Ralgnal
P106 - Lowry
P106 - Crawford
P106 - Naiko
P106 - Largs
P106 - Brownlow
P106 - Coalinga
P106 - Webb
P106 - Crockery
P106 - Coolawang
P106 - Moonanga
P106 - Mokami
LOWS01 - Lowry South 1
LSW01 - Lowry South West 1
WTL01 - Wittelbee 1
RSB01 - Rosebay 1
P91 - Mokami
PAR - Parsons
PARA - Paralana
PEN - Pennington
PER - Perlubie
PERS - Perlubie South
RAP - Rapid
RIN - Rincon
SCF40 - Rig Camp
SHG - Shale
SIL - Silver Sands
SNE - Snelling
STA - Stanleys
STR - Streaky
STU - Stunsail
TANT - Tantanna
WFO - Western Flank Oil Pipeline
WIN - Windmill
so I can get the 1st drop down of course no problem and this is in Cell F3 on a different sheet.
I want to put the 2nd drop down right next to it in cell G3 (Non-Well/Facility/Camp or Multi-Well/Facility/Camp Codes)
I enter =INDIRECT(SUBSTITUTE(F3," ","_"))
and it is not working.
but I also want list 3 to work based on the choice in dropdown 1. List 3 (Well, Facility & Camp Codes) also needs to be in cell G3.
example if the user chooses Non-Well/Facility/Camp or Multi-Well/Facility/Camp Codes from the dropdown in list 1 in cell F3, then I want the list "NonMulti" to appear in G3.
If the user chooses Well, Facility & Camp Codes from the dropdown in list 1 in cell F3, then I want the list "FacilityCodes" to appear in G3.
Hope this makes sense.
thanks in Advance
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Can you please upload a sample file so we don't have to spend time trying recreate the file.
Br,
Anders
December 7, 2020
Offline
Hi
I have attached the sample file.
columns for the dropdowns are in RED. "Facility Test" and "Area Test" on sheet "CPB Doc Request".
the lists for the dropdowns are on sheet "CPB_AreaCodes" (you may need to scroll across) named 1st "Facilities", 2nd "NonMulti" and 3rd "FacilityCodes".
thanks
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Thank you for uploading a sample file.
What version of Excel do you use? Do you have access to the new dynamic array functions? If so then this can be done without too much alteration of your data structure. You can for example check out this post to see if the solution there works for you.
Br,
Anders
December 7, 2020
Offline
Hi
thanks for your response.... I read everything and was good, but did not solve my problem
I experimented with the Filter Function which works great in a cell on the sheet, but you cannot use this is a dropdown, this then led me to XLOOKUP which is just perfect and worked well but it only returns the first row in the drop down, not all the rows that equal the search/match.
for example:
in the drop down I have this code
=XLOOKUP(F13,N11:N92,O11:O92)
F13 is the lookup Value, which looks for it in N11:N92 and returns the value from O11:O92.
this works great but I need to return all rows from O11:O92, that it matches in N11:N92.
then I read somewhere that you can add the Pound sign # to do this, but this just came up with an error.
so not sure how do to do this please.
and then I also read that you can add the $ sign, so if you add to the table it will automatically include the data in the dropdown. so I added this, but not sure if this is correct either.
=XLOOKUP(F13,$N$11:$N$92,$O$11:$O$92)
Kind Regards
Trusted Members
October 18, 2018
Offline
Have you looked at this tutorial? https://www.contextures.com/xl.....val02.html
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I am writing this while Alan's post is awaiting moderation, so it might be that I give the same answer as he does.
In worksheet CPB Doc Request, cell G3, create a data validation list using below formula:
=INDIRECT(IF($F3="Non-Well/Facility/Camp or Multi-Well/Facility/Camp Codes","NonMulti","FacilityCodes"))
I hope this helps you forward.
Br,
Anders
Answers Post
December 7, 2020
Offline
Hi,
Yes I have already watched all the tutorials etc, and I still couldn't make it work.
but your code above worked perfectly!!!
so grateful for your help.
Kind Regards
December 7, 2020
Offline
Hi Again
following your example above, I tried to create another drop down as per below.
=INDIRECT(IF($D3=“Corporate”, “tbl_Non”, IF($D3=“Multiple Permit / Facility Documentation”, “tbl_Multi”, IF($D3=”Specific Permit/Facility Documentation”, tbl_Permits, “tbl_Facility” ))))
based on the value in cell D3, I want it to return the corresponding table in E3 as a drop down. The above didn't work and I don't understand why.
The error message keeps saying that a "Named Range I specify cannot be found" but they are all there... I am so confused.
my Table Names are correct.
and the dropdown values in D3 are correct and correspond correctly.
I did a nested IF function, am I using the incorrect function?
thanks
1 Guest(s)
