• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Dependant Dropdown|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Dependant Dropdown|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Dependant Dropdown
sp_PrintTopic sp_TopicIcon
Dependant Dropdown
Avatar
Wonder Woman
Member
Members
Level 0
Forum Posts: 30
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
1
October 27, 2021 - 10:54 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
October 27, 2021 - 9:11 pm
sp_Permalink sp_Print

Hello,

Can you please upload a sample file so we don't have to spend time trying recreate the file.

Br,
Anders

Avatar
Wonder Woman
Member
Members
Level 0
Forum Posts: 30
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
3
October 28, 2021 - 10:09 am
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
October 29, 2021 - 6:15 pm
sp_Permalink sp_Print

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

Avatar
Wonder Woman
Member
Members
Level 0
Forum Posts: 30
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
5
November 3, 2021 - 5:14 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 132
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
6
November 4, 2021 - 3:43 am
sp_Permalink sp_Print

Have you looked at this tutorial?  https://www.contextures.com/xl.....val02.html

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
November 4, 2021 - 6:25 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Wonder Woman
Member
Members
Level 0
Forum Posts: 30
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
8
November 4, 2021 - 2:21 pm
sp_Permalink sp_Print

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

Avatar
Wonder Woman
Member
Members
Level 0
Forum Posts: 30
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
9
November 5, 2021 - 3:32 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Rocco Pinneri, Alexandra Sullivan
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27274

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.