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

Подписаться 27 тыс.

Просмотров 7 тыс.

50% 1

★ 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

Опубликовано:

3 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 43

@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 👍

@osoriomatucurane9511 17 дней назад

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 15 дней назад

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

@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.

@babisflou87 Месяц назад

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

@ExcelOffTheGrid Месяц назад

Thanks, I’m glad it was helpful!

@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.

@jerrydellasala7643 Месяц назад

Nicely done!

@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.👍

@kebincui Месяц назад

Excellent tips 👍. Thanks Mark

@ExcelOffTheGrid Месяц назад

Thanks Kebin 😁

@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.

@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 😁

@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.

@stevemorris4938 Месяц назад

If you're on office 365 Spilled arrays are now the ONLY way to go!

@ExcelOffTheGrid Месяц назад

I had a project recently where I used that method (a lot). It’s probably my favourite.

@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.

@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.

@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 👍

@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.

@Askmacoy 7 дней назад

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

@ExcelOffTheGrid 2 дня назад

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!

@senghongtan9357 24 дня назад

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 24 дня назад

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