Today, a new podcast series: "Things that I Learned at Trainertage", by Bill 'MrExcel' Jelen.
I spent a week in Lucerne Switzerland at the 8th annual Trainer Days. This is a team of Microsoft Office Trainers who began getting together once a year to teach each other new tricks in Office. Today, they allow others to attend. I was their guest speaker for Trainertage 2013. Now, the obvious problem: I don't speak German. But they were patient. I spoke slowly, and everyone got along well. My friend Tony Dejonker helped with translation during my first session, but it was decided that my "Slow English" was good enough for the remaining three sessions.
First observation: doing training for other trainers is tougher than my normal sessions. I always learn a new trick or two from the audience in a normal session; usually someone in row 2 shows me up. I expect it, and have a prize for it. At Trainertage, it was less than 1 minute and 30 seconds into my presentation when the first trick came from row 2, and more flooded in from row 2 all night. Some even from row 1 and row 3!
Second observation: When I would watch another trainer's session in Deutsch, I would learn new stuff. I couldn't understand the language, but I would pick up things that I had not seen in the U.S. These new friends of mine all learned from books in German and attended their own sessions and had learned a whole new set of tricks. I joked that I was going to steal these for the podcast, and they were all cool with that. "It is not stealing, it is sharing". They learned from me, and I learned from them.
In my first episode of Things That I Learned at Trainertage, I lead off with a trick from the mother of Trainertage; Tanja Kuhn. Yes, you might be smart enough to build a dynamic range using OFFSET and COUNTA, but you won't have to anymore with this clever twist on using Ctrl+T. Today, in Episode #1632, Bill shows us a new dimension in Dynamic Ranges.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
I spent a week in Lucerne Switzerland at the 8th annual Trainer Days. This is a team of Microsoft Office Trainers who began getting together once a year to teach each other new tricks in Office. Today, they allow others to attend. I was their guest speaker for Trainertage 2013. Now, the obvious problem: I don't speak German. But they were patient. I spoke slowly, and everyone got along well. My friend Tony Dejonker helped with translation during my first session, but it was decided that my "Slow English" was good enough for the remaining three sessions.
First observation: doing training for other trainers is tougher than my normal sessions. I always learn a new trick or two from the audience in a normal session; usually someone in row 2 shows me up. I expect it, and have a prize for it. At Trainertage, it was less than 1 minute and 30 seconds into my presentation when the first trick came from row 2, and more flooded in from row 2 all night. Some even from row 1 and row 3!
Second observation: When I would watch another trainer's session in Deutsch, I would learn new stuff. I couldn't understand the language, but I would pick up things that I had not seen in the U.S. These new friends of mine all learned from books in German and attended their own sessions and had learned a whole new set of tricks. I joked that I was going to steal these for the podcast, and they were all cool with that. "It is not stealing, it is sharing". They learned from me, and I learned from them.
In my first episode of Things That I Learned at Trainertage, I lead off with a trick from the mother of Trainertage; Tanja Kuhn. Yes, you might be smart enough to build a dynamic range using OFFSET and COUNTA, but you won't have to anymore with this clever twist on using Ctrl+T. Today, in Episode #1632, Bill shows us a new dimension in Dynamic Ranges.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Excel tricks that I learned at TrainerTage.
This is episode # 1632 Dynamic Range Without OFFSET Okay, well hey this this month January 2013. I had an opportunity to spend a week in Lucerne, Switzerland.
And this was the 8th annual event where a bunch of trainers Microsoft Office trainers get together to train each other and they also allow others to come.
This is a beautiful event and it's in German so they invited me as their guest speaker.
I spoke English very slowly, but I learned so much just watching them even though they were teaching in German, I was like wow hey these are cool tricks, so periodically here on the podcast, we're gonna show things that I learned at TrainerTage in Lucerne Switzerland. All right, so here's the deal today. We want to have a dynamic validation list here, so data validation.
We want that list to come from a back sheet And I want to be able to add new items to the bottom of this list, so that's a dynamic named range, right.
We're going to have to use the OFFSET function. Which is going to make my head hurt. I never get it right.
It's horrible check this out. I'm going to take this list and I make it into a table with Ctrl T Yes, my table has headers. Click OK and that actually creates a table name.
Unfortunately, that table name doesn't help at all.
We can't refer to that table name in data validation, but what we can do is choose the items in the table like this, and then I'm going to create a new named range.
I'll just call it MyProducts Enter So now excel understands these five cells are called MyProducts When I come back here to sheet 1, I go to Data Validation.
That's Alt+D+L and I'm going to allow a List and the source is = MyProducts Good so far of course you can change the input message and Error Alert if you'd like.
Click OK and so now we get to choose from the list.
Good enough I've shown that before but here's the amazing thing because I had made this range into a table with Ctrl T, when I now add new items they are automatically added to the bottom of my list all right, so set up a table with Ctrl T and then create a named range from the original cells as the table expands the name of the the list expands as well, absolutely amazing. Now it doesn't work if you had done the table name for some reason just like that's not hooked up so =ProductTable Click OK Yes, we found a problem with this formula. Not allowed to use that but by using a named range first it grows automatically.
I saw that and I said wow that is a great trick. I'll never have to use OFFSET again.
OK well hey I want to send my thanks to Tanja Kuhn.
This trick was from Tonya, Tonya is also the person who's been doing TrainerTage for the last 8 years.
Tonya is the author of a great video. The brain video excel 2010 pivot tables that's in German.
So if you have German be sure, if you understand German be sure to check that out also next year January 17th through the 21st 2014, in beautiful Lucerne if you have an opportunity to be in Lucerne. This is a great seminar. The TrainerToga seminar especially if you understand German.
They amazingly they invited me back. So we'd love to see you there.
hey I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
This is episode # 1632 Dynamic Range Without OFFSET Okay, well hey this this month January 2013. I had an opportunity to spend a week in Lucerne, Switzerland.
And this was the 8th annual event where a bunch of trainers Microsoft Office trainers get together to train each other and they also allow others to come.
This is a beautiful event and it's in German so they invited me as their guest speaker.
I spoke English very slowly, but I learned so much just watching them even though they were teaching in German, I was like wow hey these are cool tricks, so periodically here on the podcast, we're gonna show things that I learned at TrainerTage in Lucerne Switzerland. All right, so here's the deal today. We want to have a dynamic validation list here, so data validation.
We want that list to come from a back sheet And I want to be able to add new items to the bottom of this list, so that's a dynamic named range, right.
We're going to have to use the OFFSET function. Which is going to make my head hurt. I never get it right.
It's horrible check this out. I'm going to take this list and I make it into a table with Ctrl T Yes, my table has headers. Click OK and that actually creates a table name.
Unfortunately, that table name doesn't help at all.
We can't refer to that table name in data validation, but what we can do is choose the items in the table like this, and then I'm going to create a new named range.
I'll just call it MyProducts Enter So now excel understands these five cells are called MyProducts When I come back here to sheet 1, I go to Data Validation.
That's Alt+D+L and I'm going to allow a List and the source is = MyProducts Good so far of course you can change the input message and Error Alert if you'd like.
Click OK and so now we get to choose from the list.
Good enough I've shown that before but here's the amazing thing because I had made this range into a table with Ctrl T, when I now add new items they are automatically added to the bottom of my list all right, so set up a table with Ctrl T and then create a named range from the original cells as the table expands the name of the the list expands as well, absolutely amazing. Now it doesn't work if you had done the table name for some reason just like that's not hooked up so =ProductTable Click OK Yes, we found a problem with this formula. Not allowed to use that but by using a named range first it grows automatically.
I saw that and I said wow that is a great trick. I'll never have to use OFFSET again.
OK well hey I want to send my thanks to Tanja Kuhn.
This trick was from Tonya, Tonya is also the person who's been doing TrainerTage for the last 8 years.
Tonya is the author of a great video. The brain video excel 2010 pivot tables that's in German.
So if you have German be sure, if you understand German be sure to check that out also next year January 17th through the 21st 2014, in beautiful Lucerne if you have an opportunity to be in Lucerne. This is a great seminar. The TrainerToga seminar especially if you understand German.
They amazingly they invited me back. So we'd love to see you there.
hey I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
