Hello,

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

Br,
Anders

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

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

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

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

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

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

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