This is an INSANELY useful video. It's so clear, the examples are so good (esp that data validation one), and the hash makes spilled list so much more useful. Thank you Mynda! I'm going to teach my spouse this one as she would find it very helpful.
Mynda, you have been "SPILLing" many secrets of Excel already, which have helped many like me immensely and this video is cherry on the cake! Thank you! 😊👍
You are greater than the greats. Thank you and Mrs. Leila for this good work. You have widened the ocean of my excel from nearly knowing nothing to nearly an expert level. I sometimes think I live inside excel. Thank you for the good work.
Mynda, thank you. Besides, I have solved this tutorial by using INDIRECT function as below: Source=indirect($C17$) We are creating name list for each category and by using indirect function, we are converting this named list into data validation. Named List-1: Clothing = {Socks\Socks\Jerseys\Tights} Named List-2: Components= {Handlebars} Named List-3: Bikes= {Road Bikes\Mountain Bikes} Named List-4: Accessories = {Pumps\Helmets\Tires and Tubes} when we are shifting category name from the C17 cell, indirect brings named list which matches with C17 name.
- [0:00] 🎯 Introduction to the spill operator in Dynamic array formulas for Microsoft 365 and Office 2021, enabling results to spill across multiple cells. - [0:30] 📊 Spilled arrays are identified by a blue border, indicating dynamic spill behavior. - [0:59] 🔄 Referencing spilled arrays in formulas using the spill operator "#" allows automatic adjustment to array changes. - [2:24] 📝 Spill operator can be utilized in data validation, enabling dynamic dropdown lists based on spilled array contents. - [7:05] 📌 Spill operator can also be applied to defined names, offering versatility in formula references and data validation.
Thanks Mynda, already applied 😁 I have tour codes that I regularly add to and I want them to appear in my payment schedule - now I add the code and it is in my dropdown list!
Hi ma'am, I have seen very few people on the internet with such a clear demonstration of what one wants to portray. The screen recording software is really impressive. The audio is super clear and audible. When trying to create videos, I generally struggle with the audio part, though I get the video right. It would be really kind of you, if you share with us the audio and video recording tools (both hardware and software) which you ae using to publish your content. If it is a trade secret which you do not wish to share, then i am also ok with it. Thank you for creating such wonderful content. Please keep up the good work. Thanks again.
I'm a heavy user of Excel to simplify SAP reports and shows to our directors, many of them improved by your tutorials, thanks a lot, could you please create a video with MRP examples? I think there is an opportunity to develop all your knowledge in a file
@@MyOnlineTrainingHubMRP is a material requirement planning, is used un every logistic departament to calculate the balance of the inventories, it works for fished goods or components, it could be very simple or very complex depend on your specific needs, Excel is the only tool that allows you customize that information.. If you want I can send you some examples I would be more than happy to work with you
Amazing to be able to dynamically change the selection, video was super helpful. However it kind of kills me at 4:24 you are still relying on copying and pasting a static set of columns (I get probably outside scope of video). Is there any way to make this part dynamically expand too?
Thank you for this video. It clarified a few doubts. Question, could I just drag alongside the transposed data as well? But I guess it would not refresh if new data is added, would it?
I didn't know adding # to xlookup formula can spill all the available results without showing the blank cells '0' However there is one thing quite confusing, unlike the simple xlookup formula, when we change the category choice, the product menu using data validation does not change at the same time until we touch the menu scroll button....
Yes, the product menu not updating immediately is a little off putting, but it's because you already have data in that cell, so it's not until you go to change it that Excel can present you with a different list of options.
@@MyOnlineTrainingHub it’s gonna be highly appreciated if you could make another video demonstrating that. I’m sure most ppl need more than two dependent dropdown lists 😏. Thank you 🙏
For the return array, could you not dynamically reference to the lookup array that uses a hash, using other Excel formula?. That way no array is hardcoded? Or is that not yet possible. Great work btw!
Yes, but it depends how you're using it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you. From what I understand this is useful for Lookup functions for a changing list. But wouldn't this function also work like lookup functions but instead of using cell ranges, we use table names? Given that setting a table automatically updates the cell ranges when it is referenced in a formula. Thank you so much. Regards, Financially responsible OwO
You can only use a function that returns a reference in the data validation source field. The table structured references would return all items in the table column being referenced, not only those relevant to the category selected.
@@MyOnlineTrainingHub once a company begins to use integrated software infrastructure in conjunction with Office it makes changing versions very expensive. Regardless, this is the first time I watched one of your videos. You do very well. Continued success.
I get #Ref error when using # operator with xlookup. E.g. =Xlookup (A11, $B$8:$O$8,$B9:$O$9,"Not Found",0)# If the syntax is incorrect, what should it read in this example?
The # operator references a spilled array. I presume B8:O8 and B9:O9 are not spilled arrays. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub It appears I need to first register for your forum. Besides user name, first name etc. I am asked to enter password "twice". There is only 1 line for entering password. Not sure how I am to enter it twice. Could you clarify? Source data (i.e. from where the Xlookup is to be performed) is in one sheet, and the lookup is to be performed in another sheet. Number of rows in the source sheet can fluctuate. E.g..: could be 50, 100, 1000, 10000 rows etc. In the 2nd sheet, I'd like to have Xlookup only perform lookup for the no. of rows in the source sheet. Wondering if and how the hash operator can be used in this situation.
Sri, not sure what you mean by the forum asking you to enter your password twice. Please reach out via email so we can help you: website at MyOnlineTrainingHub.com
This is an INSANELY useful video. It's so clear, the examples are so good (esp that data validation one), and the hash makes spilled list so much more useful. Thank you Mynda! I'm going to teach my spouse this one as she would find it very helpful.
Thanks for your kind words!
The data validation example was GOLD. Thanks, Mynda!
Glad it was helpful!
Mynda, you have been "SPILLing" many secrets of Excel already, which have helped many like me immensely and this video is cherry on the cake! Thank you! 😊👍
😁 Thanks, Vijay!
You are greater than the greats. Thank you and Mrs. Leila for this good work. You have widened the ocean of my excel from nearly knowing nothing to nearly an expert level. I sometimes think I live inside excel. Thank you for the good work.
So great to hear 😊 congratulations on your hard work!
Mynda, thank you.
Besides, I have solved this tutorial by using INDIRECT function as below:
Source=indirect($C17$)
We are creating name list for each category and by using indirect function, we are converting this named list into data validation.
Named List-1: Clothing = {Socks\Socks\Jerseys\Tights}
Named List-2: Components= {Handlebars}
Named List-3: Bikes= {Road Bikes\Mountain Bikes}
Named List-4: Accessories = {Pumps\Helmets\Tires and Tubes}
when we are shifting category name from the C17 cell, indirect brings named list which matches with C17 name.
Thanks for sharing, Emre.
Hello Mynda,
Thanks for showing various ways we could use spilled arrays. Keep it up!
Regards
Ravi
Thanks, Ravi!
- [0:00] 🎯 Introduction to the spill operator in Dynamic array formulas for Microsoft 365 and Office 2021, enabling results to spill across multiple cells.
- [0:30] 📊 Spilled arrays are identified by a blue border, indicating dynamic spill behavior.
- [0:59] 🔄 Referencing spilled arrays in formulas using the spill operator "#" allows automatic adjustment to array changes.
- [2:24] 📝 Spill operator can be utilized in data validation, enabling dynamic dropdown lists based on spilled array contents.
- [7:05] 📌 Spill operator can also be applied to defined names, offering versatility in formula references and data validation.
Excellent as always! Thank you Mynda!
Glad you enjoyed it!
Thanks Mynda..very helpful as your explanations are so clear (as always :-) ).
Thanks so much 🙏😊
My heart started beating at minute 4:58 .... and then at 5:20 = 🤯🤯🤯🤯🤯
Amazing Mynda !!! 😁👌👌👌
Thanks so much!
Thanks for #-ing it out Mynda! Happy to report that I've put most of these tricks to good use at least once in my work 😊
😁 great to hear, Imran!
Thanks Mynda, already applied 😁 I have tour codes that I regularly add to and I want them to appear in my payment schedule - now I add the code and it is in my dropdown list!
Great to hear!
it saves lot of time. Practice! will ensure to use it when and where it is required. Thanks
Sure does 😊
Absolutely amazing video and tricks, found it after two years still not too late 😂!
Your channel deserves more subscribers since it is really useful for excel users. Thanks for your videos. 😊
So nice of you, Ranjit! Please spread the word 😊
@@MyOnlineTrainingHub Sure. Will do.😊
Thanks Mynda perfect timing for a clever top tip #saves so much time
Glad it was helpful, Peter!
🤯🤯Blow minded... It's totally Insane! And YES, I know will make my life easier....
Awesome to hear!
Hi Mynda. Some great # tricks there! As always, thanks for sharing :)) Thumbs up!!
Thanks so much, Wayne!
Thank you! That answered my question in a previous video completely!
Great to hear!
the # is very useful for recording macro or script , great tutorial
Glad you think so!
what a video ! ,thanks for your effort to share such amazing things.
So nice of you 🙏😊
I’ve been using offset to do the same thing with data validation. I like your way better.
Great to hear, Bobby!
Thanks for this video.
Very useful!
Great to hear 😊
Very helpful. Thanks Mynda
Cheers, Matt!
Amazing video , very helpful
Glad you think so!
very good and clear instruction
Thank you!
Excellent Mynda!
Cheers, Chris!
Hi ma'am,
I have seen very few people on the internet with such a clear demonstration of what one wants to portray.
The screen recording software is really impressive. The audio is super clear and audible.
When trying to create videos, I generally struggle with the audio part, though I get the video right.
It would be really kind of you, if you share with us the audio and video recording tools (both hardware and software) which you ae using to publish your content.
If it is a trade secret which you do not wish to share, then i am also ok with it.
Thank you for creating such wonderful content. Please keep up the good work.
Thanks again.
I use Camtasia Studio and a Samson microphone. Good luck with your videos!
@@MyOnlineTrainingHub Thanks for sharing. Please let us know the model and type of your microphone. I would like to buy it...
It's a G Track microphone, but it's pretty old now and you can probably get better ones.
Excellent as usual.... Thanks.
Glad you enjoyed it
Awesome every time. I have learned so much from you!
Glad to hear it!
Wow, this is awesome! Thank you so much!!
Glad you like it!
Definitely going to try this one!
Great to hear!
Hi Mynda!Really Helpful Tutorial..Thank You :)
Great to hear, Darryl!
I'm a heavy user of Excel to simplify SAP reports and shows to our directors, many of them improved by your tutorials, thanks a lot, could you please create a video with MRP examples? I think there is an opportunity to develop all your knowledge in a file
Great to hear, Sergio! I don't know what MRP is.
@@MyOnlineTrainingHubMRP is a material requirement planning, is used un every logistic departament to calculate the balance of the inventories, it works for fished goods or components, it could be very simple or very complex depend on your specific needs, Excel is the only tool that allows you customize that information.. If you want I can send you some examples I would be more than happy to work with you
The most usefull video.....,
Glad you think so!
fantastic! Congrats 🌹❤️🙏
Thank you so much!
Amazing to be able to dynamically change the selection, video was super helpful. However it kind of kills me at 4:24 you are still relying on copying and pasting a static set of columns (I get probably outside scope of video).
Is there any way to make this part dynamically expand too?
Hi Leif, Adam suggested this formula using OFFSET: =XLOOKUP(C17,$G$17#,OFFSET($G$17#,1,0))#
You r the excel God
My my my.. this is amazing!
Glad you liked it, Bruce!
So much easier, quicker, and less error prone, than using the old indirect() with named ranges method. 👍
Glad you liked it, Henry!
Mind. Blown... 😳
Glad you liked it, Dave!
This is amazing 👏
Glad you liked it!
Love to see your amazing tricks Mam
So cool, love it! Thank you!
Glad you like it!
Thanks Mynda
My pleasure :-)
Amazing 💥💥
Thanks 🔥
😳… Smart & Useful
Glad you think so, Jose!
So fantastic.
Thanks so much 😊
Nice one!!
Thank you 😊
OMG you are so smart 🤓
You're too kind :-)
This is great...!😃
Thanks, Shakthi!
Amazing!
Thanks so much!
Another great video.
But why when Part is added to the Table does Clothing count change from 4 to 5?
It's not counting 'clothing' it's counting the number of items in the spilled array in cell G3. i.e. cells G3:G7.
Thank you for this video. It clarified a few doubts. Question, could I just drag alongside the transposed data as well? But I guess it would not refresh if new data is added, would it?
Not sure what you mean by 'drag alongside the transposed data as well'.
😃i didn't know it !
Glad it was helpful!
Another great video :)
Glad you enjoyed it!
I didn't know adding # to xlookup formula can spill all the available results without showing the blank cells '0'
However there is one thing quite confusing, unlike the simple xlookup formula, when we change the category choice, the product menu using data validation does not change at the same time until we touch the menu scroll button....
Yes, the product menu not updating immediately is a little off putting, but it's because you already have data in that cell, so it's not until you go to change it that Excel can present you with a different list of options.
That works fine and easily with two dependent dropdown lists. The question here, how to use this method to get multiple dependent dropdown lists 🤔🧐?!
Rinse and repeat. Use the second data validation item to extract a list of items for the 3rd data validation list.
@@MyOnlineTrainingHub it’s gonna be highly appreciated if you could make another video demonstrating that. I’m sure most ppl need more than two dependent dropdown lists 😏.
Thank you 🙏
Merci
You're welcome 😊
For the return array, could you not dynamically reference to the lookup array that uses a hash, using other Excel formula?. That way no array is hardcoded? Or is that not yet possible. Great work btw!
Hi Sachin, Adam suggested this formula using OFFSET: =XLOOKUP(C17,$G$17#,OFFSET($G$17#,1,0))#
@@MyOnlineTrainingHub Perfect, that then solves the problem for any unknown amount of future expansion?
Can we use # cell reference while using Index-Match? It throws a #value error currently
Yes, but it depends how you're using it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you. From what I understand this is useful for Lookup functions for a changing list. But wouldn't this function also work like lookup functions but instead of using cell ranges, we use table names? Given that setting a table automatically updates the cell ranges when it is referenced in a formula. Thank you so much.
Regards,
Financially responsible OwO
You can only use a function that returns a reference in the data validation source field. The table structured references would return all items in the table column being referenced, not only those relevant to the category selected.
# - spill array looks like the "xldown" command in VBA.
👍
Making me nuts trying to find an answer, is there, or is there not, an equivalent to using {} to define an array as you would in Sheets?
You can enter arrays in Excel surrounded by curly braces. e.g. =SUM({5,10,7,8})
Advanced!
why this # trick not working in my excel
It only works on ranges returned by dynamic array formulas and for that you need Microsoft 365.
I like your vid but my company does not use 365 or 2021.
That's a shame.
@@MyOnlineTrainingHub once a company begins to use integrated software infrastructure in conjunction with Office it makes changing versions very expensive. Regardless, this is the first time I watched one of your videos. You do very well. Continued success.
These tips are legitimate, but useless unless they can be used immediately in one's immediate work. Otherwise they are forgotten.
As you start to use dynamic array functions more and more the # sign will be like any other sheet reference. i.e. something you use all the time.
I get #Ref error when using # operator with xlookup. E.g.
=Xlookup (A11, $B$8:$O$8,$B9:$O$9,"Not Found",0)#
If the syntax is incorrect, what should it read in this example?
The # operator references a spilled array. I presume B8:O8 and B9:O9 are not spilled arrays. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub It appears I need to first register for your forum. Besides user name, first name etc. I am asked to enter password "twice". There is only 1 line for entering password. Not sure how I am to enter it twice. Could you clarify?
Source data (i.e. from where the Xlookup is to be performed) is in one sheet, and the lookup is to be performed in another sheet. Number of rows in the source sheet can fluctuate. E.g..: could be 50, 100, 1000, 10000 rows etc. In the 2nd sheet, I'd like to have Xlookup only perform lookup for the no. of rows in the source sheet. Wondering if and how the hash operator can be used in this situation.
Sri, not sure what you mean by the forum asking you to enter your password twice. Please reach out via email so we can help you: website at MyOnlineTrainingHub.com
@@MyOnlineTrainingHub Hello. I've sent the email as suggested