Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

i keep getting a spill# after ive done the counta and second offset
=OFFSET(Sheet1!$A$1,1,MATCH($A$3,Sheet1!$A$1#,0)-1,COUNTA(OFFSET(Sheet1!$A$1,1,MATCH($A$3,Sheet1!$A$1#,0)-1,20,1)),1)

sheet 1 holds my source data and its all in row 1 $A$1# - its also dynamic using a transpose formula from another page , is that the issue? ( =TRANSPOSE(UNIQUE('1st - Floors'!A2:A1000)) ) from a data range called floors

Leila, great video, but I think there is a minor error in one of the formulas on your website which is: =OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,) It looks like that comma after the last "1" should be deleted. I was getting an error message and then deleted it and it worked perfectly. :)

Hi, in Offset tab height is only 20, it's not dynamic. Anyway i solved it by using " COUNTA($A:$C)" in replace of 20 . Can help it any alternative ways

Thanks for the video - the second approach is much easier

I am using same formula =OFFSET($BD$1,1,MATCH($C$9,$BD$1:$BJ$1,0)-1,COUNTA(OFFSET($BD$1,1,MATCH($C$9,$BD$1:$BJ$1,0)-1,20,1)),1) but as and wen I select values in first cell .. in second cell values are getting reduced gradually. the second row has 13 values.. like when I select first cell value - it is displaying all 13 , next row - when I select it is displaying 12 only.. and at certain point nothing is getting displayed..please help

Hot Tip: If they don't need to be dynamic, just select a huge box of all your data, then *F5 to select Constants, then Ctrl+Shift+F3 and now you won't have any "blank" options*. Simpler, but not dynamic. Much simpler if you don't need dynamic lists; but if you do need dynamic lists, this video is a life saver.

I want to create "product ingredients with dropdown menu" in Google Sheets. Any ideas?

Incredibly complicated

Hi,can you guide me as a new person learning to work with excel.I need to learn excel 101 and master it.

Offsetption!

I like your way of explaining which can be understood well by all audiences.👍

Any idea how to make a dynamic drop down list but exclude not only empty rows but also rows with certain text or color for example?

background music disturbts lot - sorry to say

excelllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllent

life saver!

Thank you so much this is amazing!

Thanks so much for going over multiple methods! The Offset method worked best for my purposes, great tutorial 👍

Simply Incredible....No words for your excellence. I thought I was good in excel. Today i realized...i know nothing 🙂

Your Excel videos are the best! Very easy to understand! THANK YOU!

very super drop down list both formula offset and index match👍

Quick formula and data val trick I learned today, which utilizes referenced cells that spill:

Create the cell that will spill horizontally and/or vertically > Create the offset / counta formula you've got here, but wrap it in "unique" formula, inside a "sort" formula.
The short version is this if you're using just one column (not searching which column to use): =SORT(UNIQUE(OFFSET(B3),1,0,COUNTA(B:B)-1,1)))

Here's the nifty part about creating the DataVal dropdown from that without a lot of extra text, or copying, or issues, which allows list creation from spilled cells that are horizontal AND/OR vertical:
In the DataVal pop-up box, select "List", then go to the "Source:" field and type the absolute ref of the spilling cell, but add a hashtag after it. Like this: =$A$4#

That is a really short way to tell the list to include everything from the ref'd cell, instead of another section of long, complex info. It also saves time if you want to update the referenced cell's formula, like if it ends up including new columns or something in its info-searching chain.

Note: By wrapping the earlier function in the SORT and UNIQUE formulas, you essentially remove all duplicates and auto-sort the list, and that is carried into the dropdown automatically.

Such a clear explanation! crystal clear.... ❤

I subscribe as I love all your tips. Your dependent drop down list videos were very helpful. Unfortunately, the solution doesn’t seem to work with Excel’s data validation in tables. Did I miss something? Regardless, I will continue to benefit from your knowledge and excellent teaching skills.

Love it an understatement in the feeling I have toward your Excel knowleage. Thank-you so much, you explain these concepts better than a paid course.

If we select a value from the secondary list ( say the 5th or 6th item ), then go ahead to change the value is the primary list, the value in the secondary cell doesn't update automatically to the first item of the new primary value lists., Any workaround for this

Thank you

You are the best Leila👍👍

Hi Leila, Love your videos. plz can u help me as how i can get data of columns in a single drop own list. means in one sheet i have monthwise product consumption and on another sheet i want the drop down list of months where product consumption changes monthwise so that i can make graph on month basis on this sheet.

I liked second approach better. Much cleaner.

Hello Leila, it working for one cell only whenever I try to put the same in another row it's not picking the results from their drop down list it's always picking from the frist one only could you please help me on this?

Dear Leila, How to create drop down list based numerical values.

Exactly what I needed right now! Very helpful!

IT SAYS I PUT TOO MANY ARGUMENTS :(
=OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,O15)

Hi Leila, am facing issues.Its not changing dynamically. The dropdown list is not changing after selecting the 2nd row lists, like if i select Games and Kryptis. And when I change to produtivity, then the dropdown list remains Kyptis, its not changing to productivity Group list dynamically. Also, in your video, you are not selecting the other dropdown list. Request your support.

How to do that searchable if the productivity is very long

How to get a drop down list with 2 columns?

Dear Leila, i have done exactly as your tutorial, but my data validation list only shows 8 values! what is the problem?

Thanks!

After putting counta formula my drop down list stops working....& If I didn't do counta it's works fine. What could be the reason ?

Hi Leila, love your videos! My question is, how would this be applied to horizontal lists? I tried using the filter formula in the data validation but I am getting errors.

Great Hack. Thanks

hellow mam I am small freelancer worker please give me this work.

Thank you for your video, I'd like to ask what if we change values in dropdown list tables and I want these new values automatically to be updated in the big table (in which I selected the old values), how can we do that? Thank you.

Hi Leila, your videos r amazing. I'm trying to make a dependent drop down list with multiple names but it's not done, plz make a video of "dependent drop down list" with multiple names like heading "productivity items name". Because in dependent drop down list not accepting "space". Its accept only "underscore sign".. thanks

Any excel lover who love excel will not resist subscribing your channel.

Cool stuff Leila. Thank you very much

So index can return array...I don’t know that...thanks a lot...