Restrict values in dropdown list by IF and Lookup Formulas

[mjlhyj]

Hi, I am looking to see if there's any formula that I can add under data validation that can restrict the dropdown list values based on conditions? I would like the dropdown list to show all values under the Name row as long as the 2019-calculated row's values show as either B2 or A1.

Current data table:

Capture.JPG

The drop down list should show 'A' and 'B' as values. What formula can I use under Data Validation? Do I need VBA for this instead?

Thanks!

[Richard Buttrey]

Can you be more precise please and give us some examples, preferably uploading a workbook along with some notes.

It's not at all clear to me what values you expect to see and how other rows in your table might affect the drop down

[mjlhyj]

Hi Richard,
I've uploaded the file in the attached.

In the first sheet, I'm planning to have a list of names horizontally to complete the fields below.

In the 2nd worksheet, under the Internal Successor Name, i wish to have have a dropdown list which shows the list of names that only have the grade B2 or A3 in worksheet 1.

I've tried to input a Index-Match formula using Data Validation in cell F7, but it only shows 1 value instead of 2.

Hope this clarifies further.

Thanks!

[JeteMc]

Hello mjlhyj and Welcome to Excel Forum.
The following proposal, modeled in the attached copy of the file, employs a helper column, header lst_Name, which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using:

Please Login or Register  to view this content.

The source of the data validation then references the helper column using: =OFFSET('Potential Assessment'!$U$6,0,0,SUMPRODUCT(--('Potential Assessment'!$U$6:$U$8<>"")),1)
Let us know if you have any questions.

[Richard Buttrey]

I can't do any better than point you to Debra's Contextures web site where you'll learn all you'll ever need to know about dependent drop down lists - and more besides.

[mjlhyj]

Thanks JeteMc, much appreciated!

[mjlhyj]

Thanks for your help Richard! Really useful information in that site. That will probably mean I should create a helper column anyway to make use of dependent drop down lists.

[JeteMc]

You're Welcome. Thank You for that feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.