Excel Dynamic Arrays: Data Validation List Dropdown: Unique, Sorted & No Blanks EMT 1523 

THANKS! Never heard about this function but now I start using it. Really excellent.

So, I used this formula to eliminate zero at work, but needed it on a personal file. I searched for the RU-vid guru and thought it was in two other's. But couldn't find it. Of course, Mike had it here, in less than 3 minutes! Thanks for the formula and how quick you were able to get to it!

This Man is borderline genius.

This is so cool Mike!! Thanks for the continued EXCEL-lence with your videos

After searching for many days. I found it you're the best. Appreciate your work!

Wonderful stuff! This is what these functions were designed for. Love the tweak to omit 0 where you have blanks.

Thanks Mike, you bailed me out again! Very cool! Appreciate all you do!

Another great one...Thanks Mike.

Thank you for this great video 📹

As always.... ITs AMAZING Mike and thank you so much for sharing this :)

Thanks Mike :) Can't wait to try all of these when they are released.

Mike, I just got the office insider and now have access to these new formulas, the game has changed! Thank you so much for putting out these videos to learn from!

At the minute I'm doing this using a power query table which is set to auto update every minute and an offset formula in the data validation window. These dynamic array formulas are so much easier! I have insider edition at home but since it's a work spreadsheet I'm talking about, I'm still waiting. Hope they come out with it for the masses soon. Thanks as always for the great content Mike.

Love the new Dynamic Array functions in O365...and the hash data validation tip was great!

Just Perfect ....Thanks Mike

i wanned to cilck like at least 10 times but its just counting once here... Profound Respect

Thanks for another great tip Mike 👍

WOW! is all I can say to Dynamic Arrays. Game changer in so many ways. Be great if they could populate and expand Tables to fit the content without the #Spill error though. Fingers crossed that is in the pipeline. It would also be great to use these dynamic dropdowns and have them referred to by a Table or Chart Filter also..kind of like a Dropdown Slicer like in Power BI... Great job as always Mike. Excel just got even more FUN.

love that shortcut drop down list

Thanks Mike for this EXCELlent video

It's kind of hilarious how happy this makes me :-))) Thanks Mike for explaining, and thanks to the whole team for making it easier and more interesting for us users ;-)

I love this one :). Awesome!

Very thanks, my teacher.

Great as always!

This is so cooool. Goodbye offset and counta. Thank you for the playlist too!

Just Amazing.

Great man! Thumbs up

Not all heroes wear capes

Awesome.

Thanks Mike

Hi Mike, how can we give numbering to an extracted list using a dynamic array formula like unique or filter that changes its size depending upon variables?

Dear Mr. Mike good morning, I'm strongly sorry that I was in a bad tow weeks i had been in a toothache pain as well as i had an exam so I couldn't watch any lesson of yours.

@excelIsFun .... hello i`m using your older video using aggregate function to extract all non Blank cells from a column and create a new column with all cells values in a new list (with blanks ignored & list being condensed)..... however google sheets doesnt like aggregate function. and for some reason =filter doesnt exist in my ( excell ) office 365, (which apparently google sheets should be ok with.. Do you know of a way to get that filtered list to work in both ms excell and google sheets??...no drop downs or anything... cheers.

Data validation is a useful utility available in Excel.

nice video.

Nice dynamic arraysss...work is more easy

Wonderful. Many thanks.

Excellent Kevin, Is there a way to search data validation cell in a table? Means use A2 and type something to select a player name? , I tried it, but only works in a range no in a table. Would you help me, please?

I am using Office 365 pro plus but I can't see these functions in the library. Are these functions only available for insider Office 365? If yes, do you know when these will be available to all Office 365 version

All of the videos on the new dynamic arrays are solving the same data validation problem. The first cell we pick the category and the second cell shows the associated subcategory drop down. BUT what if we wanted to fill an entire table with a column for the first category and in a second column we select the subcategory from a drop down. How do the references work as we move down the rows of the table?

Nice video as always. I am wondering if the formula can be input to the Data Validation directly?

For a simple non-searchable drop down list I would use the functions in a different order, SORT(UNIQUE(FILTER - no need to repeat the SORT(UNIQUE. ;)

can you carry this out further, to set up smart dependent dropdown lists? I have a list of supervisors and want to create a list of employees assigned to a supervisor so that when i select the supervisor the dependent drop down gives the employees assigned to that supervisor.

How did you put the highlights alternately?

Hi, professor, I am using Excel 2016 how do I get sorted drop down list

I'm trying to make a do down list for a table that shows years multiple times. I input =sort(unique(Table1[year])) and it tells me that the function isn't valid. I have 365 @ExcelIsfun please help!!!

@ExcellsFun +ExcellsFun Hey Mike! I'm struggling here -- I'm running on a military (government) laptop and they severely limit the ability to utilize specific things and/for Excel -- so I'm stuck with Excel 2016 and macros are 50/50 -- whether they're allowed or not... So when I first even try to start this off, =FILTERXML is the only option available. I'm trying to create a Flow Chart, of sorts. Pick a selection (start, 1), only certain options are available to the selection, and when the second selection (2) is made, there are only specific options available for that selection (3) -- and I'd like it to go out maybe to 4-5 selections down the flow... but the list is finite. There's 2 columns. *Example:F = Final InspectionR = Awaiting Pick UpU = Picked Up, must be Closed firstS = Closed, Technically Completed **Each one of these digits has it's own "code description" ** --- select F F can be changed to 1, 2, 3, 5, B, C, E, F, G, I, J, K, R, S, U --- select R R can be changed to 4, 5, G, M, U --- select U U can be changed to 5, 8, G, S -- select S S can be still be changed to 5, 8, R -- but this is the end System Code. Notice that between F, R, U, and S -- there are identical digits.

02:51 no more offset 😍

Hi Mike please show me how to create a drop down list from another excel file an it should work when the data file is closed.

Hello sir. I am facing 1 issue.after applying unique function still it five me 2 value of each product name. Example apply unique function on planet name and it give me 2 time name earth in same column. What should i do to get single name after applying function

Hi Mike.. another great one. Question: Could you skip pointing the validation criteria at a spilled range living on the worksheet by creating a named range (say rng_Filtered_List) with the refers to equal to your FILTER SORT UNIQUE formula and then using =rng_Filtered_List as the source in the validation criteria for the data validation input? If so, will it still react dynamically as the source data changes, such as the addition of a new unique record to the underlying list? I'd test this my self, but don't yet have the dynamic array formulas. Just curious. Thanks again and Thumbs up!

Mike - will the dynamic array formula work inside the Data Validation list, without having to spill and refer to the unique list on your spreadsheet?

How do we use the list on other sheet?

I'm guessing if you were to make the video that would explain how to do this to non-365 users, it might take more than 4:45

Great to know that the # reference works in the Source field. Can the dynamic array formula also be written directly in the Source field? How about form controls (e.g. ComboBox) - both MS Forms and ActiveX - can dynamic arrays be used with these as well?

is there no sort, filter and unique function in excel 2016? I typed in excel but the function does not appear.

Why it is not possible to write the Filter(Sort(Unique))) formula right in the Data Validation Formula field and we need to create helper columns instead ?

I found 1 problem it does not keep the format for the cell like for date "11/3/18" instead it puts the 43407.

Hi Mike, is it possible to use Dynamic Array formulas to create a cascading validation list? We do a lot of accounting system migrations where accounts in the old accounting system need to be mapped to a new chart of accounts. So what we are trying to do is to take the list of the old accounts and as we select say for instance Income Statement then all the Income Statement groups (Revenue, Cost of Sales, OPEX, etc) are filtered in the validation list. When I select for instance Cost of Sales then the validation list dynamically updates with the Cost of Sales categories (Direct Labour, Direct Material, Finished Goods, etc). The following video demonstrates the principle of what we are trying to achieve. Except we have to cascade 4 or 5 Levels into the account structure. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5nb84p2wX-c.html For Example: Old Account (5000 - Technical Staff Salaries - Permanent) - Level 1: Report Type -> Income Statement - Level 2: Report Group -> Expenses - Level 3: Report Category -> Cost of sales - Level 4: Account Group -> Direct Labour - Level 5: Account Category -> Direct Labour - Permanent - Level 6: New Account Name -> COS: Labour cost (Permanent) The above mapping needs to happen in a cascading manner based on the selection of the previous level. Hope it makes sense?

It's a great solution but when will we get this update :(

We can do sorted unique and searchable drop down list using this: =SORT(UNIQUE(FILTER(A5:A29,ISNUMBER(SEARCH(F5,A5:A29,1)),"")))

well yes, the "Unique" formula gives a lot of options and workarounds. But in the old Excel version - no way there to do this trick? Well yes, I could make an Excel VBA button and use VBA and maybe somehow work it around like that, but well, with formulas - no way?

This is ridiculous!!! I need this fix but I don't have 365!!

3

1

2