instead of filter, change the if statement to produce NA() then use the TOCOL() function to filter out the non-visible rows.. =TOCOL(UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,NA())))),3) Tocol makes a easy filter to get rid of blanks and errors, so by putting the NA() function in the false of the if, is easier to do then the LET and Filter()
@ExcelOffTheGrid
6 ай бұрын
Nice, I like it ✅ - this might be the 6th method I’ve seen for this formula now. Interresting use of TOCOL. Maybe I need to do some performance testing.
@davidferrick
Ай бұрын
Very nice indeed.
@henrypatner1260
Ай бұрын
Can you explain how the disconnected table works? Are you using unique to get the items of a column, then hardcoding them into a table, then generating a slicer from this hardcoded table?
@ExcelOffTheGrid
Ай бұрын
They are loaded into a Table as part of a Power Query refresh. So it updates whenever the data is updated.
@henrypatner1260
Ай бұрын
@@ExcelOffTheGrid Thank you. Do you have videos on the topic? Still hazy on what the workflow looks like and other videos on disconnected tables don't seem to be similar to the workflow described (as other discussions revolve around Power BI and Dax rather than use in excel).
@colinburrows88
6 ай бұрын
What I've used before is a helper pivot table (also connected to the slicer). This then shows just the selected items. Much, much simpler, so what am I missing??
@maciejkopczynski55
6 ай бұрын
There are always at least 3 ways of achieving equivalent results. Go for the solution you like the most. It is also always fun to learn new techniques to apply when you get stuck using the one you are used to performing.
@ExcelOffTheGrid
6 ай бұрын
I’ve used both methods too. The key differences are: 1) The Pivot Cache which the PivotTable is generated from does not exist in the calculation chain, so we need to rely on a refresh. This table method is more dynamic as it only requires the calculation chain. 2) A PivotTable is not an object we can reference directly. We have to reference a range which bigger than the PivotTable and hope it is big enough for the future. This method uses Tables which can be directly referenced and have auto expansion built-in. 3) By default PivotTables maintain a cache of previous field items, even if no longer I the data set, so may create the need to clear the cached items from time-to-time. So, the PivotTable approach increases risk of error and the need for manual maintenance. Once the fxSlicerSelection function set-up, I think it will be easier than building helper PivotTables.
@colinburrows88
6 ай бұрын
@@maciejkopczynski55 Agreed. I did like and learn from the video. I just wanted to know if the technique in the video accomplishes something more than using a helper pivot table.
@colinburrows88
6 ай бұрын
@@ExcelOffTheGrid Very clearly explained. Thanks very much.
@arindambhattacharya8127
6 ай бұрын
once again - there is a much easier way to achieve your objective than all of these complications. Simplicity is key to success. Also a small advice - before diving into function and formula - make the objective of the tutorial clear - for me it wasn't clear as to what was the objective of making this video. "Let", "Byrow","Lambda" - before using these like a recorded script - it is essential to explain what am I trying to achieve in the first place
@ExcelOffTheGrid
6 ай бұрын
What is the easier way that you mentioned?
@arindambhattacharya8127
6 ай бұрын
@@ExcelOffTheGrid a pivot table(helper) or simple filter formula. I did this and got it very easily without complicating the objective
@ExcelOffTheGrid
6 ай бұрын
A helper PivotTable uses an object which is outside of the calculation chain. So it introduces 2 additional risk areas: 1) Having to reference an area bigger than the PivotTable in the hope the data doesn’t exceed the range. If it does it gives the wrong values. 2) Having to refresh the PivotTable.when data changes (which is de-risked if using Power Query) adds a manual step into the process. If a user forgets, the values are wrong. The method in this video avoids both of those issues. In the video we create a reusable formula which can be copy/pasted to another workbook. I believe that applying that formula is now significantly easier than having to create a helper PivotTable each time. Just my opinion.
@arindambhattacharya8127
6 ай бұрын
@@ExcelOffTheGrid The amount of time and complexity involved in this is not the worth of yield in this case..just my opinion
@ExcelOffTheGrid
6 ай бұрын
Cool. Thanks for watching the video.
@davidferrick
Ай бұрын
Without using a LAMBDA =LET(uniqueList, UNIQUE(FILTER(PartsUsed[Br Branch Num], SUBTOTAL(3, OFFSET(PartsUsed[Br Branch Num], ROW(PartsUsed[Br Branch Num])-MIN(ROW(PartsUsed[Br Branch Num])), 0, 1)))), uniqueList) But honestly I like yours.
@JoseAntonioMorato
25 күн бұрын
Dear Mark, Your formula, filtering data
@ExcelOffTheGrid
25 күн бұрын
The example is showing how to adjust the filter context. So somebody might filter for something, but you can show them something different, using their selection as the basis based.
@EricHartwigExcelConsulting
6 ай бұрын
Stellar video! Thank you for sharing!
@ExcelOffTheGrid
6 ай бұрын
Thanks - glad you enjoyed it. 😁
@IvanCortinas_ES
6 ай бұрын
Absolutely efficient. Great field work. Thank you very much for sharing Mark.
@ExcelOffTheGrid
6 ай бұрын
Thanks Ivan. I hope you can put it to good use.
@ianl1052
3 ай бұрын
Fascinating. The only problem I find with LAMBDA and its help functions is how they make the formula almost unrecognisable from the original formula. My workaround, insert a couple of extra columns and break the formula down into parts to see how it changes. I also added some =FORMULATEXT cells to see at a glance how the formula changes. It works for me. Obviously, I downloaded your "...Complete" file and left it untouched to compare my WIP in the "...Start" file with the finished product. I'll need some practice.
@ExcelOffTheGrid
3 ай бұрын
Pre-LAMBDA adding columns to the data was pretty much the only way. That used to be my preferred method. However, for me it broke the basic principles of separating data, calculation and presentation. It adds a calculation element (the slicer selection) back into the data (the table). So I prefer the LAMBDA option, because I think it’s the right way to structure a spreadsheet.
@ianl1052
3 ай бұрын
@@ExcelOffTheGrid It's just a temporary fix until I fully get my head around LAMBDA. Once I've practiced a few times, it will no longer be necessary. As I'm retired, it's not as though I'll need it for work or anything. I'm just doing these tutorials to keep my brain active in an attempt to keep the dreaded dementia at bay.
@shaneshort3216
6 ай бұрын
Dark sorcery this is =) Thank you!
@ExcelOffTheGrid
6 ай бұрын
You're very welcome!
@aneeq7531
2 ай бұрын
Great Video
@ExcelOffTheGrid
2 ай бұрын
Glad you enjoyed it
@tibibara
20 күн бұрын
Mark, honestly, I don't wanna praise you in all of my comments, because it already sounds boring or people just think I'm just one of your friends or something like that....🤣🤣🤣🤣🤣 But it's brilliant content again! Brilliant! And I just really-really enjoy that you approach exactly the same, practice-oriented way as I do regarding Excel and formulas...so thanks again!
@ExcelOffTheGrid
20 күн бұрын
I'm OK with praise... much better than the alternative 🤣 I must admit that when I discovered these techniques, even my brain was 🤯. It has completely changed how I approach a lot of interactivity now.
@tibibara
20 күн бұрын
@@ExcelOffTheGrid I learned a lot from you, thanks again ;-) I just watched your Slicer vs Pivotby video and trying to create a generic version, but currently I think it will be at least 2 options: 1, to disconnected table slicer and 2, a general filter argument using boolean logic to filter a whole table.
@avvabaldur
6 ай бұрын
Wow, amazing technique... 365 is now sorcery compared to older versions. It seems so much easier to ask the computer to do anything these days.
@ExcelOffTheGrid
6 ай бұрын
That's a good term... "365 is now sorcery"... it certainly feels like that. It gives us so many better ways to solve problems.
@noelgreen6198
6 ай бұрын
Excellent post as usual, thank you.
@ExcelOffTheGrid
6 ай бұрын
Thank You. I'm glad it was useful.
@yulinliu850
6 ай бұрын
👍❤
@ExcelOffTheGrid
6 ай бұрын
😁
@balakrishnaj1713
5 ай бұрын
Thankyou, this is very helpful
@safuwanch
6 ай бұрын
I don't know what to say, wonderful video, 💞💞 I suggest that it would be better if you zoom little bit while working in a specific part of a screen like writing formulas , it helps to focus more , thank you 😊
@ExcelOffTheGrid
6 ай бұрын
Thanks for that feedback about the level of zoom. I appreciate it you letting me know.
@txreal2
6 ай бұрын
Awesome! 👍 I learned force row calculations using BYROW (), use LET (), and use IF inside LAMBDA. Thanks! Been subscribed & followed
@ExcelOffTheGrid
6 ай бұрын
Awesome! Thank you! 👍
@Jim-zm6fw
6 ай бұрын
Hi Mark: A great video and terrific solution. This topic has been top of mind for me lately. Thank you! You're a great presenter -- clear and concise.
@ExcelOffTheGrid
6 ай бұрын
Thanks Jim - I hope you can put it to good use.
@stevereed5776
6 ай бұрын
Very useful, thank you
@ExcelOffTheGrid
6 ай бұрын
Glad it was helpful!
@kebincui
6 ай бұрын
Super👍❤
@ExcelOffTheGrid
6 ай бұрын
Thanks Kebin. 😁
@excelenthusiasts
6 ай бұрын
Hi, Here's another way to get the selected items =LET(rng,A2:A12,FILTER(rng,BYROW(rng,LAMBDA(a,SUBTOTAL(103,a)))))
@ExcelOffTheGrid
6 ай бұрын
That's nice. I created about 4 different calculation for this... and I've created a better version in minutes. Good Work 👍
Пікірлер: 54