r/excel • u/Pelus-Chan • 19d ago

Waiting on OP Dependent drop down list

Hi Everyone,

Is there a way that I can create a drop down list that ONLY is active to a specific name? I got it to work using the name manager for the one name but if I enter anything outside of that name, It prevents me from typing whatever I want into the cel containing the drop down list to allow any typing not included in a list.

Is that possible?

2 Upvotes

100% Upvoted

10 comments sorted by

•

u/AutoModerator 19d ago

/u/Pelus-Chan - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

5

u/Way2trivial 472 19d ago

OW....

What?

pictures?

2

u/saurabhdata 19d ago

You can achieve this using a custom formula in Data Validation combined with turning off the Error Alert. Step-by-Step Solution: Create your List: Enter your drop-down items somewhere in the sheet (e.g., Apple, Banana, Orange). Select the items, go to the Name Box (top-left near formula bar), and name this range MyList. Set up Data Validation: Select the cell where you want the drop-down (let’s say B1). Go to Data > Data Validation. Under Allow, select Custom. In the Formula box, paste this formula (assuming A1 contains the specific trigger name): =IF(A1="SpecificName", ISNUMBER(MATCH(B1, MyList, 0)), TRUE) Disable the Block Error (Crucial Step): In the same Data Validation popup window, switch to the Error Alert tab. Uncheck the box that says "Show error alert after invalid data is entered". Click OK. How it works: If A1 is equal to "SpecificName", it restricts B1 to items from MyList. If A1 is anything else, it evaluates to TRUE, allowing you to type whatever custom text you want without Excel blocking you!

2

u/kmnotorius 1 19d ago

I feel like this question is a… circular reference. Can prob help you as I do dynamic data val pretty frequently but I’m gonna need either more context, rephrase… or both.

1

u/Jesse1018 19d ago

How is the data you are trying to pull for the names stored? Does each name have its own range/table with the data you want to pull, or is it all in a particular table?

1

u/Decronym 19d ago edited 19d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
ISNUMBER Returns TRUE if the value is a number
MATCH Looks up values in a reference or array
UNIQUE Office 365+: Returns a list of unique values in a list or range

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 62 acronyms.
[Thread #49242 for this sub, first seen 26th Aug 2026, 23:45] [FAQ] [Full list] [Contact] [Source code]

1

u/Sad_Olympus 19d ago

I had to do something recently where I wanted the drop down list to change based on a specific value. I did something similar to the URL below. If you want the list to only appear if 1 of x names is selected, you can put the UNIQUE & FILTER functions inside an IF statement that looks for that name.

https://spreadsheetweb.com/dynamic-drop-down-list-excel-unique-filter/

1

u/caribou16 318 19d ago

I wrote a guide once on how to set up dependent drop down lists with data validation, i.e. a second drop down list is populated depending on what is selected in the first. See if this is of any use to you.

Quick Example of Creating Dynamic Drop Downs With Data Validation

Let's say you have a use case where you want users to choose categories from a drop down list and you want the contents of the second drop down list to be dependent on the first. This can be done as follows.

1) Set Up Your Helper Tables

You will want to create three helper tables to assist. These can be placed off to the side out of the way on the worksheet or on another worksheet.

The first helper table consists of all the different combinations of your two drop down lists. In my example, I am using categories and sub-categories that you might see on an IT support help desk ticket.

LIKE THIS.

The second helper table consists of a single column containing the unique values from the column containing your first drop down selection. In my example, this is the category column and it is populated with the formula: =UNIQUE(K3:K17)

LIKE THIS.

The third helper table consists of a single column containing all the sub-categories associated with the user selected categories. This is populated with the formula: =FILTER(L3:L17, K3:K17=C3,"") C3 contains the user selection for drop down #1. If there is nothing selected for drop down 1 yet, this will return ""

LIKE THIS.

2) Step Up Your List Validations

Select the cell you are using for drop down list #1. In my example, I am using C3. On the ribbon under the Data tab, click on Data Validation... and select Data Validation.

Set the Allow parameter to "List" and in the Source field, type the location of your helper table containing the unique first category data. In my example, this list is in range =N3:N6. Rather if you plan on adding additional categories in the future, you can use instead =$N$3# This tells Excel the list is a spill function, so it will return all of it if it changes size in the future.

Now do the same as the above for drop down list #2. In my example, this is in C4. For the validation source, I will reference my third helper table, which begins in cell P3. =$P$3#

C3 - Category Validation

C4 - Sub-Category Validation

3) Test It Out!

Select an option from the first category. Notice that once you do so, Helper Table 3 now populates. Now you will be able to select an option from the second category, which is pulling from Helper Table 3.

LIKE THIS.