Great gift. I hope Microsoft includes some of these features as native by 2025. Mass replacements are used quite a bit, for example. Thanks for sharing, Mark.
This man is clearly capable of magic. Genius! Fun fact: I have just missed your first function. I would have needed it just 6 hours before, and now my workaround seems to be useless. But I will definitely set up all your functions!
★ Get the example file ★ Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders/ File Reference for this video: 0243 5 Custom Functions
Great functions Mark! Thanks for offering them. I can see some immediate uses for them. Note that I had created a similar custom function to TAB_NAME that I call SHEETNAME. One issue with both is that they will return the #VALUE! error in a file that has not been saved yet. I updated my function to output an error message that gives a clue to the solution: #FILENOTSAVED! Here is my version, called SHEETNAME() (it is not volatile): LAMBDA([reference], LET(excelfilename,IF(ISOMITTED(reference),CELL("filename"),CELL("filename",reference)), IF(excelfilename="","#FILENOTSAVED!",TEXTAFTER(excelfilename,"]",-1,,,"")) ) )
Thanks - I'm glad you like the functions. Unfortunately, as much as I love VBA, it is being blocked by more and more ant-virus software, Microsoft is adding security restrictions, and VBA doesn't exist in new Outlook... so... I think it is the beginning of the end.
Thank you, Mark, for this and other useful videos, you have been sharing with us I also have a rather universal custom function and wish to share it with you and maybe with Excel community. I can send it to your assessment, if you don't mind...
First of all, thank you Mark for your work. There is a lot of videos on YT with this topic but your presentation is the best. I would like to ask you about your solution. You are using formulas with _xleta. I am not able to put formulas with _xleta into operation. Is there anything to allow to work with it?
Really useful functions. Regarding the Tab Name function, do you know if there is a way to reverse it and have the tab name update based on the value in a cell?
Hi Mark, please tell me how to make the "Custom Formats" group on your Home Tab. I find myself ctrl-1-ing too much to get jjjjmmdd, or jjjjmmdd uu:mm or dddd dd-mm-jjjj and some other ones... Also a little warning for folks that want to use the TAB_NAME, like this it only works on an English Excel, as "filename" is different in other languages (in Dutch it's "bestandsnaam"). Is there a way to get the 'under water' name for a tab (the name that never changes, in VBA)
You may also find that TAB_NAME produces the #VALUE! error until the file is saved. I had created a similar function that I called SHEETNAME and it has the same issue. It's minor since you'll save the file eventually, but you might want to add a special output that gives a clue to the problem. I made the function output #FILENOTSAVED! as an error when the file hasn't been saved yet.
Thank you, Mark! The new amazing content is here. This is truly a revolution in custom functions, opening our minds to new possibilities.
Yes, there is a lot in Custom Functions - I'm finding new possibilities all the time
Thank you, Mark. As always a great presentation of truly useful approaches. Thanks for the great Holiday gifts.
Great gift. I hope Microsoft includes some of these features as native by 2025. Mass replacements are used quite a bit, for example. Thanks for sharing, Mark.
Thanks Ivan, I'm glad you like it. 😁
This man is clearly capable of magic. Genius! Fun fact: I have just missed your first function. I would have needed it just 6 hours before, and now my workaround seems to be useless. But I will definitely set up all your functions!
Capable of magic... probably not.
Capable of applying helping others to get a deep understanding of how Excel really works... hopefully. 😁
Harry Potter of Excel 😂😂😂
★ Get the example file ★
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders/
File Reference for this video: 0243 5 Custom Functions
These are excellent functions. You are a life saver.
Thank you very much - not only for the useful information, but above all for your excellent presentation.
Glad it was helpful!
Thanks for the great solutions for very typical Excel queries. They will become part of my go-to tools from now on.
Great to hear!
This would be a great series of videos
Oooookay. Now you impressed me. Great job!
Yay! Thank you! 😁
Wow, these are really brilliant and useful. I’d never thought about trying build the filter_selection one but that opens up so many uses!
That's one of my favourites too. If we create dummy tables using Power Query, we are free to use slicers with formulas. 👍
Thank you from Oman
Great functions Mark! Thanks for offering them. I can see some immediate uses for them. Note that I had created a similar custom function to TAB_NAME that I call SHEETNAME. One issue with both is that they will return the #VALUE! error in a file that has not been saved yet. I updated my function to output an error message that gives a clue to the solution: #FILENOTSAVED!
Here is my version, called SHEETNAME() (it is not volatile):
LAMBDA([reference],
LET(excelfilename,IF(ISOMITTED(reference),CELL("filename"),CELL("filename",reference)),
IF(excelfilename="","#FILENOTSAVED!",TEXTAFTER(excelfilename,"]",-1,,,""))
)
)
That's a nice addition - I like it 👍
Great🎉,it's a new year gift for us sir
Happy new year 😁
Thanks Mark. Happy New Year!
Thanks Chris, Happy New Year.
Thanks❤❤❤
I really admire that you started to share the UDF collection with the community, and long live VBA.
Long live Office Script 🙄
Thanks - I'm glad you like the functions.
Unfortunately, as much as I love VBA, it is being blocked by more and more ant-virus software, Microsoft is adding security restrictions, and VBA doesn't exist in new Outlook... so... I think it is the beginning of the end.
Office Scripts is the future 👍
Very good
Thank you
You're welcome
Thank you Mark!
Happy New Year 2025
You are the UDF GOD. Thanks!
Thanks 😁
Thank you, Mark, for this and other useful videos, you have been sharing with us
I also have a rather universal custom function and wish to share it with you and maybe with Excel community. I can send it to your assessment, if you don't mind...
First of all, thank you Mark for your work. There is a lot of videos on YT with this topic but your presentation is the best. I would like to ask you about your solution. You are using formulas with _xleta. I am not able to put formulas with _xleta into operation. Is there anything to allow to work with it?
10:58 So, this is similar to the CALCULATE() function in DAX, right?
CALCULATE applies an aggregation to a filtered table - so I think of it differently.
5:58 Can’t we also use the FILTER() function?
It is the FILTER function with some advanced options included to work with multiple columns and partial strings.
Really useful functions. Regarding the Tab Name function, do you know if there is a way to reverse it and have the tab name update based on the value in a cell?
Yes, that possible. You will need some VBA to do that. Just Google it there are lots of resources.
how about default number formats in Pivot Tables?
Can we expect a full excel course including VBA 😌
Our training covers everything users need (over 60 hours of courses) - some of which includes VBA.
@ExcelOffTheGrid Okay how much it costs?
Hi Mark,
please tell me how to make the "Custom Formats" group on your Home Tab. I find myself ctrl-1-ing too much to get jjjjmmdd, or jjjjmmdd uu:mm or dddd dd-mm-jjjj and some other ones...
Also a little warning for folks that want to use the TAB_NAME, like this it only works on an English Excel, as "filename" is different in other languages (in Dutch it's "bestandsnaam").
Is there a way to get the 'under water' name for a tab (the name that never changes, in VBA)
You may also find that TAB_NAME produces the #VALUE! error until the file is saved. I had created a similar function that I called SHEETNAME and it has the same issue. It's minor since you'll save the file eventually, but you might want to add a special output that gives a clue to the problem. I made the function output #FILENOTSAVED! as an error when the file hasn't been saved yet.
Very true about the "filename" parameter in other language. Thanks for adding that. 👍