The spill range option was a great cheap & dirty method. I was interested to use the named ranges but was not quite sure how to apply that between worksheets (feel free to spoon feed me if you like!). Thank you for your very helpful tutorial!!
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!!!
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 !
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.
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.
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!
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?
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.
@@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!
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.
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?
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.
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?
The spill range option was a great cheap & dirty method. I was interested to use the named ranges but was not quite sure how to apply that between worksheets (feel free to spoon feed me if you like!). Thank you for your very helpful tutorial!!
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!!!
So you’ve tried pretty much everything 👍
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 !
Named range + Table is solid. Much less risky than Named range + OFFSET.
I really like the format you used here. Comprehensive, pros and cons. Thanks!
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.
Great detailed tutorial!
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.
Method #3 is a pretty solid option.👍
Super tips. To the point presenting all options available. Thank you!
Thanks, I’m glad it was helpful!
Excellent tips 👍. Thanks Mark
Thanks Kebin 😁
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!
That’s not on the list at the moment. But maybe one day 😁
Thanks for these options. Can you do a simular explanation of the conditional formatting function? These keep on breaking during use of the sheets.
Hii!! I m using 2013 is there any other way around for that?
Very helpful, thanks Paul
Nicely done. Thank you. Defined Name looks to be the best to me.
Yes, they are a pretty solid option. Good choice.
Does it have to be myList? for the 2nd method. Cause its not working on my end
It should be whatever your Table is called.
Nicely done!
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?
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.
@@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!
Great video, thanks Mark
Thanks, I'm glad you enjoyed it
You are teaching me the not working style also.. it's awesome..
What not to do is maybe more important than what to do.
@@ExcelOffTheGrid Yes u r absolutely right.. I'll take this word from you.. Thanks.
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.
Anyone know how to get the named ranges to pop up on a Mac? F3 doesn't work. command+function+f3 doesn't either. Thanks
You are the best .
Thank you.
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?
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.
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?
Just checked, it breaks even with standard references. Typing manually isn't an option with the 256 character limit.
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
Figured #4 out all by myself and then started using it everywhere (ok, in that specific project)
#4 is pretty intuitive- good choice 👍
If you're on office 365 Spilled arrays are now the ONLY way to go!
I had a project recently where I used that method (a lot). It’s probably my favourite.
I use 4th, find it the best.
That’s probably my favourite too.
=I like this#
Thanks 😁