U can make a list from a dynamic function. Just add a # after the reference in the data validation source at the start of the list. Also, put rows function in the sequence function then reference the names next to it to number them dynamically.
I love FILTER, UNIQUE and SORT - real game changers! However, these new formulae are all very well if, as you say, your customer has 365. If not, for me it's back to my trusty Excel 2003 - which works on anything - and pages of VBA, of course!
@@TigerSpreadsheetSolutions Thank you! Now these new formulae are available I try and use them in combination with the old formulae as much as possible and only rely on VBA as a last resort. Previously I'd have used VBA for any kind of analysis. The problem with that is I've found VBA slows to a crawl if you have several sizable spreadsheets open at the same time, whereas formulae don't seem to be affected. I assume it's a memory issue. Well, perhaps formulae are affected but they're so fast you don't notice!
Did a doubletake when i heard my name. Forgot all about that spreadsheet. Great vid! Only think keeping me from using dynamic arrays is my job being on Excel 2019.
Hey Chris hi! Thanks for the video! In the case of Unique - in order to create drop down lists, after typing the UNIQUE and making the spill - would you color the cells of the spill to be more visually striking? and if the DDList is growing due to the spill, how would you keep coloring the next cells below (new spilled ones) etc.? Thanks Man Cheers!
Great comment - and you’ve touched on one of my criticisms of dynamic arrays that I actually edited out of this video! It would really help if the array simple took the formatting of the top row (where the formula is) - but that doesn’t happen. So you’d have to ‘pre-format’ cells for the UNIQUE formula to add values to the bottom as new data comes into the dataset. It’s disappointing for me because a (THE) point of dynamic arrays is they relieve us of this kind of ‘capacity estimation’ issue. This is why I use VBA is possible.
Good video but I think you give Filter a bad rap!- really simple to sum a column for example, just use index to pick the column and wrap it in sum. Also a 1 blank filter criteria can be handled quite easily using isblank.
@@TigerSpreadsheetSolutionsone of my favorites is assuming my data is in a table doing =SUM(FILTER(Table(Column1,Table[Column2]="value")) And of course having that value be from one of the sorted unique lists drop-downs.
It’s not really fair to look a these in isolation. You can do so much analysis with the filter function now by using functions like CHOOSEROWS, CHOOSECOLS, DROP, etc.
That’s why I said it’s not fair to look at them in isolation. The reality is, if you have Excel 365, you have access to all of the new dynamic array functions anyway. So your argument doesn’t really make much sense.
The focus is the original release of 5-6 formulae and the promise they would 'change everything'. The underlying point is that no new formula will 'change everything' in Excel, which is why people should focus on different parts of Excel practice, not just formulae. I've been arguing this for about 10 years and I fully understand it won't make sense to everybody - but for some people the idea is career-changing. All the best!
@@ae232 That's really my point - we've got to get out of that way of thinking. And yes, dynamic arrays might intrigue but VBA users have been using them forever .. Fair discussion!
I'm waiting until the Excel Gods create the FUDGE formula: =FUDGE(array, answer) Where (array) is the range of data you've given up trying to analyse, and (answer) is what you've been losing your sanity trying to achieve for the last couple of hours. FUDGE will mysteriously arrive at the answer without the headache of actually having to analyse the data... 😁😁
Can’t wait for this Steven! I found a serious point in your comment, however. The truth is: the function that does precisely what you want doesn’t exist, and will never. Which is why we need to move learning emphasis away from individual formulae and towards skills, including the softer skills of problem structuring, conceptual logic and a general appreciation of the kind of problems you’re likely to deal with in Excel.
@@TigerSpreadsheetSolutions Absolutely. The rather poor analogy I can think of, certainly in terms of Excel, is like memorising the Oxford English Dictionary but having little idea how to combine the words to make a sentence. Better to learn good sentence construction with a limited grasp of words but then look up* better words as and when you need them and expand your knowledge that way. * Was temped to use VLOOKUP or XLOOKUP here but thought that would break the analogy! :)
isn't there a customizable lambda function that basically does what you describe there? Between that and the gpt helpers i think we're pretty close to what you are describing
📺Learn Excel Formulae And Functions In 10 Minutes
tinyurl.com/4ceekebs
U can make a list from a dynamic function. Just add a # after the reference in the data validation source at the start of the list. Also, put rows function in the sequence function then reference the names next to it to number them dynamically.
Nice ideas Michelle - and do the data validation list in this way in the video. Thanks for watching!
I love FILTER, UNIQUE and SORT - real game changers! However, these new formulae are all very well if, as you say, your customer has 365. If not, for me it's back to my trusty Excel 2003 - which works on anything - and pages of VBA, of course!
A very sensible perspective I think, Steven - a pragmatic appreciation! 👍
@@TigerSpreadsheetSolutions Thank you! Now these new formulae are available I try and use them in combination with the old formulae as much as possible and only rely on VBA as a last resort. Previously I'd have used VBA for any kind of analysis. The problem with that is I've found VBA slows to a crawl if you have several sizable spreadsheets open at the same time, whereas formulae don't seem to be affected. I assume it's a memory issue. Well, perhaps formulae are affected but they're so fast you don't notice!
@@stevenlagoe7808 interesting stuff Steven - but I have to say formulae slow files down much more than VBA does, if the VBA is programmed optimally.
Production values on this one are off the charts! Great video 👏👏
If Gotham Chess and Athlean-X did Excel … minus the charisma 😊
Really nice and interesting video!
thanks, Bart! 🙌
Did a doubletake when i heard my name. Forgot all about that spreadsheet. Great vid! Only think keeping me from using dynamic arrays is my job being on Excel 2019.
Was very happy to include you DC - it’s a great example of what’s possible 👍
Hey Chris hi! Thanks for the video! In the case of Unique - in order to create drop down lists, after typing the UNIQUE and making the spill - would you color the cells of the spill to be more visually striking? and if the DDList is growing due to the spill, how would you keep coloring the next cells below (new spilled ones) etc.? Thanks Man Cheers!
Great comment - and you’ve touched on one of my criticisms of dynamic arrays that I actually edited out of this video! It would really help if the array simple took the formatting of the top row (where the formula is) - but that doesn’t happen. So you’d have to ‘pre-format’ cells for the UNIQUE formula to add values to the bottom as new data comes into the dataset. It’s disappointing for me because a (THE) point of dynamic arrays is they relieve us of this kind of ‘capacity estimation’ issue. This is why I use VBA is possible.
Good video but I think you give Filter a bad rap!- really simple to sum a column for example, just use index to pick the column and wrap it in sum. Also a 1 blank filter criteria can be handled quite easily using isblank.
I don't think combining more formulae is a 'simple' solution for many. Thanks for watching!
@@TigerSpreadsheetSolutionsone of my favorites is assuming my data is in a table doing =SUM(FILTER(Table(Column1,Table[Column2]="value"))
And of course having that value be from one of the sorted unique lists drop-downs.
It’s not really fair to look a these in isolation. You can do so much analysis with the filter function now by using functions like CHOOSEROWS, CHOOSECOLS, DROP, etc.
Were these included alongside the original release? Asking people to combine new formulae with *more* new formulae seems a lot ...
That’s why I said it’s not fair to look at them in isolation. The reality is, if you have Excel 365, you have access to all of the new dynamic array functions anyway. So your argument doesn’t really make much sense.
The focus is the original release of 5-6 formulae and the promise they would 'change everything'. The underlying point is that no new formula will 'change everything' in Excel, which is why people should focus on different parts of Excel practice, not just formulae. I've been arguing this for about 10 years and I fully understand it won't make sense to everybody - but for some people the idea is career-changing. All the best!
No one or two formulae alone will be game changing, I definitely agree with you there. But the concept of dynamic arrays in general is.
@@ae232 That's really my point - we've got to get out of that way of thinking. And yes, dynamic arrays might intrigue but VBA users have been using them forever .. Fair discussion!
I'm waiting until the Excel Gods create the FUDGE formula:
=FUDGE(array, answer)
Where (array) is the range of data you've given up trying to analyse, and (answer) is what you've been losing your sanity trying to achieve for the last couple of hours.
FUDGE will mysteriously arrive at the answer without the headache of actually having to analyse the data...
😁😁
Can’t wait for this Steven!
I found a serious point in your comment, however. The truth is: the function that does precisely what you want doesn’t exist, and will never. Which is why we need to move learning emphasis away from individual formulae and towards skills, including the softer skills of problem structuring, conceptual logic and a general appreciation of the kind of problems you’re likely to deal with in Excel.
@@TigerSpreadsheetSolutions Absolutely. The rather poor analogy I can think of, certainly in terms of Excel, is like memorising the Oxford English Dictionary but having little idea how to combine the words to make a sentence. Better to learn good sentence construction with a limited grasp of words but then look up* better words as and when you need them and expand your knowledge that way.
* Was temped to use VLOOKUP or XLOOKUP here but thought that would break the analogy! :)
@@stevenlagoe7808 brilliant 👏👏👏
isn't there a customizable lambda function that basically does what you describe there?
Between that and the gpt helpers i think we're pretty close to what you are describing