Hehe, back in the days, when there was an OR condition I filtered the first part, put some color, filtered the other part, put the same color, and filter the data by color in the end. Of course, after that, I've started either using advanced filters or simply writing a helper column with some AND(OR()) nestings - which may get very nasty. It's a great thing to re-watch a favorite video after some time! Thanks again for your great content, Gasper!
The moment i saw the advanced filter bit I felt sure it would come in useful......and today i used it to get around a limitation of countif....to filter out some repeated entries in a 170K dataset. it is and awesome feature. your explanations and passion for excel is infectious. Thank you
Wow! Your INDIRECT solution is awesome! I’ve seen so many solutions for that data validation problem but this by far is the easiest and the most clever of all! INQUIRE add-in is a gem 💎 Thanks!!! You just earned another subscriber here! 😉❤
Thanks Gasper. Great tips! Too bad Inquire is not available in standard 0365. I think it is available only in Enterprise editions. Always enjoy and learn something new at your channel :)) Thumbs up!!
Thank you Wayne. As things that it does are mostly actionable and non sticky you could find someone who has Inquire, use it and carry on at your own computer.
Many thanks, I have a question: using the custom number formatting ( @* ▼ ) show us the Black Down-pointing Triangle at the end of the selected item, the problem is when you leave the cell with data validation empty, the Black Down-pointing Triangle disappears. can you please tell us how to fix it? Regards. Mohammed, Algeria.
About comparing 2 workbooks using Inquire add in. I did not work for macro enable files for me. I had to remove vba code by saving as xlsx. Alsy hyperlinks had to be removed. Automated ‘corrections’: Macro: Sub SaveAsWorkbook() For Each ws In ActiveWorkbook.Worksheets ws.UsedRange.Hyperlinks.Delete Next ws Dim FILE_FILENAME As String FILE_NAME = InputBox("FILE_NAME name:") On Error Resume Next ActiveWorkbook.SaveAs Filename:="C:\TMP1\" & FILE_NAME, FileFormat:=51 REM 51 = xlOpenXMLWorkbook .xlsx On Error GoTo 0 'THIS RESETS ERROR HANDLING End Sub
Gr8 input Victor. It's true that Inquire AddIn is not bug-free or should I say, does not work on every version. I've had several cases where the Inquire AddIn would load (as the Ribbon would show), but none of the buttons would work... And I think it's mostly to do with software requirements that are not enforced (or you are not prompted if you don't have the correct .NET version or similar..
That is the best description of the INDIRECT function I've ever heard!
Thank you for those kind words Denise.
Hehe, back in the days, when there was an OR condition I filtered the first part, put some color, filtered the other part, put the same color, and filter the data by color in the end.
Of course, after that, I've started either using advanced filters or simply writing a helper column with some AND(OR()) nestings - which may get very nasty.
It's a great thing to re-watch a favorite video after some time! Thanks again for your great content, Gasper!
Great content! You make it easy. I'm now a subscriber!
Very useful tricks, great job 👍👍👍👍
Thanks Pierre. Glad you liked the tricks.
Fantastic! Your INDIRECT explanation was the clearest I have ever seen. Great tips. Thanks Gašper
Thanks Grainne. Thant is music to my ears! Means a lot!
Thanks Gašper. Amazing as allways. :)
Thank you Davor. I'm humbled as always 😀
The moment i saw the advanced filter bit I felt sure it would come in useful......and today i used it to get around a limitation of countif....to filter out some repeated entries in a 170K dataset. it is and awesome feature. your explanations and passion for excel is infectious. Thank you
Now that comment just made my day Paul. And the most brilliant thing is that you used it in your own business scenario. Great stuff.
Wow! Your INDIRECT solution is awesome! I’ve seen so many solutions for that data validation problem but this by far is the easiest and the most clever of all!
INQUIRE add-in is a gem 💎 Thanks!!!
You just earned another subscriber here! 😉❤
Thank you for those kind words Melanie.
Amazing tips and tricks! Made with love ;-) Thanks you, Gasper!
Thank you for those kind words Taras. Means a lot!
Really fantastic!! Thanks.
Thanks for the kind words EC.
Awesome tips, thanks!
Thanks Tom.
👍Another trick with advance filter is only required columns can be extracted from a large data set with the cop to another location feature
True Kgas. Thanks for adding that.
Great stuff! I used Indirect function but never tried in data validation.. Superb👏👏
Thank you for the kind words Sanoj.
Great video, thank you 😊
Thank you for those kind words N1ckyR.
@Excel Olympics you're welcome, looking forward to more videos 😊
Great one, Gasper. Waiting for the Extra Advanced Tips ;-)
Thanks Excel Worx. They are in the worx 😀
the best from thailand
And right back to Thailand from Slovenia :)
Thanks Gasper. Great tips! Too bad Inquire is not available in standard 0365. I think it is available only in Enterprise editions. Always enjoy and learn something new at your channel :)) Thumbs up!!
Thank you Wayne. As things that it does are mostly actionable and non sticky you could find someone who has Inquire, use it and carry on at your own computer.
I don't have "Inquire", I'm using 365, is it NOT available in this version?
By now, it should be available in all channels of 365, so it may be a version thing. Which version of Office 365 do you have?
Many thanks,
I have a question:
using the custom number formatting ( @* ▼ ) show us the Black Down-pointing Triangle at the end of the selected item,
the problem is when you leave the cell with data validation empty, the Black Down-pointing Triangle disappears.
can you please tell us how to fix it?
Regards.
Mohammed, Algeria.
Hi Mohammed. Thank you for your question. I am not sure you are going to like the answer :). What I usualy do is insert a space into that cell.
@@ExcelOlympics thank you anyway, Gašper.
You automatically get a leading underscore for the named ranged or cell, because the name can not start with a number.
About comparing 2 workbooks using Inquire add in. I did not work for macro enable files for me. I had to remove vba code by saving as xlsx. Alsy hyperlinks had to be removed. Automated ‘corrections’:
Macro: Sub SaveAsWorkbook()
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Hyperlinks.Delete
Next ws
Dim FILE_FILENAME As String
FILE_NAME = InputBox("FILE_NAME name:")
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="C:\TMP1\" & FILE_NAME, FileFormat:=51
REM 51 = xlOpenXMLWorkbook .xlsx
On Error GoTo 0 'THIS RESETS ERROR HANDLING
End Sub
Gr8 input Victor. It's true that Inquire AddIn is not bug-free or should I say, does not work on every version. I've had several cases where the Inquire AddIn would load (as the Ribbon would show), but none of the buttons would work... And I think it's mostly to do with software requirements that are not enforced (or you are not prompted if you don't have the correct .NET version or similar..