Create data validation list from Excel Table... the RIGHT WAY!

Рет қаралды 6,640

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Blog post ★
exceloffthegrid.com/using-an-...
★ About this video ★
Excel Tables expand automatically whenever new data is added. This feature alone makes Tables one of the most powerful tools within the Excel user’s toolkit.
A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would also benefit from the auto-expand feature, but they just don’t work correctly-an oversight on Microsoft’s part (in my opinion).
In this post, we examine the issues and solutions for creating an Excel data validation list from a Table.
0:00 Introduction
0:22 Overview
0:48 Standard references
1:50 Reclaim time with Excel
2:08 INDIRECT method
3:03 Named ranges
3:57 Dynamic array spill
4:40 Which method to use?
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

Пікірлер: 43

@osoriomatucurane9511 18 күн бұрын

Awesome tutorial, you are Legend. As always, diving deeper with the topic covered. I had issues with number one as I keep the list in separated sheet from the master dataset. I walked away from using table (standard standard references) for data validation. I resorted to named range with offset(). Offset (header,1,,CountA(entire column)-1,1). This one does the job. After all, Number 3 is straitforward. Named range from the table. I cant thank you enough for all your love, hard work and dedition to share your Excel mastery !

@ExcelOffTheGrid 17 күн бұрын

Named range + Table is solid. Much less risky than Named range + OFFSET.

@IvanCortinas_ES Ай бұрын

I almost always used INDIRECT, but I switched to using the renamed range. I have been using the dynamic array method since I had 365. Excellent tutorial, explaining all the possibilities. Thanks Mark!!!

@ExcelOffTheGrid Ай бұрын

So you’ve tried pretty much everything 👍

@ziggle314 Ай бұрын

I really like the format you used here. Comprehensive, pros and cons. Thanks!

@ExcelOffTheGrid Ай бұрын

I appreciate that feedback. I'm taking a new approach for the next few videos to see how people like them. Hopefully a bit shorter and more engaging, but still the same level of content.

@HyzZzz Ай бұрын

Thanks for these options. Can you do a simular explanation of the conditional formatting function? These keep on breaking during use of the sheets.

@babisflou87 Ай бұрын

Super tips. To the point presenting all options available. Thank you!

@ExcelOffTheGrid Ай бұрын

Thanks, I’m glad it was helpful!

@erikguzik8204 Ай бұрын

Method #3 was always the GO-TO way, used almost all the time with tables. The new Spilled range is a cool way too. Especially if you want that Spilled range for many uses (Game Changer) when doing formulas now. I like how you say it takes more time, and extra 30-60 seconds today, saves hours 4 months from now when you finish the project, and you add "Tiger" and then its not working. Great video as always. Thanks for the step-by-step instructions.

@ExcelOffTheGrid Ай бұрын

Method #3 is a pretty solid option.👍

@PaulEBrownbill Ай бұрын

Very helpful, thanks Paul

@vinothkumarmanoharan2317 Ай бұрын

You are teaching me the not working style also.. it's awesome..

@ExcelOffTheGrid Ай бұрын

What not to do is maybe more important than what to do.

@vinothkumarmanoharan2317 Ай бұрын

@@ExcelOffTheGrid Yes u r absolutely right.. I'll take this word from you.. Thanks.

@kebincui Ай бұрын

Excellent tips 👍. Thanks Mark

@ExcelOffTheGrid Ай бұрын

Thanks Kebin 😁

@jerrydellasala7643 Ай бұрын

Nicely done!

@DK_85 Ай бұрын

Very helpful! Thanks a l lot! I would really love to see a new excel chart video next Maybe a horizontal bridge/waterfall chart would be awesome. Thanks a lot!

@ExcelOffTheGrid Ай бұрын

That’s not on the list at the moment. But maybe one day 😁

@chrism9037 Ай бұрын

Great video, thanks Mark

@ExcelOffTheGrid Ай бұрын

Thanks, I'm glad you enjoyed it

@ryanbartlett672 Ай бұрын

Nicely done. Thank you. Defined Name looks to be the best to me.

@ExcelOffTheGrid Ай бұрын

Yes, they are a pretty solid option. Good choice.

@chuhunwei8198 Ай бұрын

How to prevent firewall error if i have 4 different data source. And i need to transform all the 4 source data first before i merge all them together.. hope you can give a good trick for this as i don’t want ask other user to click the ignore privacy level for their laptop.

@DJPejsen Ай бұрын

Just mark the databody range inside the table and add the databody range to a named range . Add the named range to the validation list

@joannabird5264 Ай бұрын

I use 4th, find it the best.

@ExcelOffTheGrid Ай бұрын

That’s probably my favourite too.

@Askmacoy 9 күн бұрын

Does it have to be myList? for the 2nd method. Cause its not working on my end

@ExcelOffTheGrid 3 күн бұрын

It should be whatever your Table is called.

@ArianneCats Ай бұрын

Hi Paul, I want to use method 3 but get stuck when filling out Data Validation List, so I click on Allow list then by filling out Source, it only gives me the possibility to enter a range and I can't find a way to fill out "MyList". You say F3, but on my MacBook that doesn't seem to work? Any suggestions?

@ExcelOffTheGrid Ай бұрын

Just type =MyList F3 just provides a way to find the range names. But you can just type in the name. There probably is a Mac equivalent, I just don’t know what that is.

@ArianneCats Ай бұрын

@@ExcelOffTheGrid I tried everything, as I thought it must be simple, but even typing in the name won't give me the list, then in the cell it just give me the name of the list. Maybe it's a setting which I have to change in Excel or maybe in my MacBook, but I keep on trying figuring that out. Thanks though!

@GeertDelmulle Ай бұрын

My preference is the Defined Name + structured reference method: robust, easy to manage, easy to understand/read. FYI: I recently applied it many times in creating a Project Log (à la PRINCE2-ish) where I had to consolidate and bring together several individual templates. PS: Déjà vu… haven’t you done a video before just like this one?

@ExcelOffTheGrid Ай бұрын

There was an older video a few years ago that didn't mention dynamic arrays. But since Dynamic Arrays are so fundamental to everything these days, I had to do an updated version.

@sledgehammer-productions Ай бұрын

Figured #4 out all by myself and then started using it everywhere (ok, in that specific project)

@ExcelOffTheGrid Ай бұрын

#4 is pretty intuitive- good choice 👍

@senghongtan9357 25 күн бұрын

I have a table that have column names that I allow users to change from a list from another table using indirect. The problem is when using excel online, it corrupts the file whenever the column name is changed. It used to work before this. Any idea if this bug will be fixed?

@senghongtan9357 25 күн бұрын

Just checked, it breaks even with standard references. Typing manually isn't an option with the 256 character limit.