The day after Christmas and you are at work? Or just checking out the podcast on your new iPod? Let's do a social networking experiment. Plus...how to autonumber rows. And...a podcast special at the MrExcel store. Watch it all in Episode 916.
This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
Transcript of the video:
Hey, alright, welcome back to the Mr...
Oh, what, hey, is that a new iPod?
Did you get a new iPod for Christmas?
Wow, that's gorgeous.
Hey, thanks for watching the MrExcel netcast on your new iPod.
Hey, do you happen to use twitter?
Are you a twitter person?
Let's try a little experiment here, I was at a Neil Young concert a few weeks ago, and I twittered that I was at the Neil Young concert, and then I went to search.twitter.com, and searched for a Neil Young and found like two or three other people who were at the same concert, twittering about the concert.
So it's the day after Christmas, I'm sorry if you're stuck working, didn't have a vacation day, so just go out to Twitter and Twitter that you're watching the MrExcel.com podcast.
Make sure that you put MrExcel.com podcast, and then come back a few hours later and go to search.twitter.com and search for MrExcel.com; we'll see who else on twitter is watching the podcast.
Maybe we can start to follow each other.
And at the very least, it's going to kill 10 minutes on the day after Christmas when we're stuck at work and no one else is in the office.
Let's switch back to Excel here.
Cool Excel trick-- this came in during one of my classes.
Someone said, "Hey, look, I have this sheet and I need to number these items." They didn't actually have text there, they had real items.
And so they would number the first one, and then build little formulas-- =1 plus the cell above me-- and copy that formula down throughout most of the spreadsheet, but wherever there was a heading they had to kind of alter the formula to say =1 plus that last number up there.
And the problem with this particular setup was that they frequently would delete items.
So it's kind of like a to-do list-- these were open issues and when they would delete, of course, we're getting reference errors throughout.
Just not a good thing at all.
So here's an alternate way to go.
We're going to take advantage of the fact that there are two count functions: The COUNT(A) function will count all of the cells that are non-blank-- so text and numbers; but the TRUECOUNT function only counts other numeric cells.
So I'm going to set up a formula here: =COUNT, and I'm going to go from (A&1 to A the cell right above me, so in this case, since I'm in A3, I'm going to use A2-- =COUNT(A$1:A$2) +1.
I only put one dollar sign there to say that we're always going to start from the top of the spreadsheet and countdown to the cell right below me, and +1 at the end.
And so in the very first cell, we're going to get a 1, and as I copy that down, it continues to count.
And what's really cool about this is that if we delete a row-- so Edit, Delete Row-- it automatically renumbers; it doesn't change to reference error.
So let's take a quick look at that function down here.
Basically, we're saying we're going to COUNT-- not COUNT(A), but COUNT-- from A$1:A12-- the cell right above us-- and add 1 to it, and it creates a nice cool way to count all the way through.
Hey, I want to thank you for watching the MrExcel podcast.
If you're here at this point, it's the day after Christmas.
Again, I'm sorry that you're working; I've got quite a deal for you.
If you didn't get any cool MrExcel stuff yesterday, I'm going to give you a secret URL here on the screen.
Go to this URL, and if you buy either the live lessons 2003, or live lessons 2007, that's a whole bunch of video tips, then I'm going to throw in-- today only-- the MrExcel podcast DVD.
So the first 630 Episodes on a single searchable DVD.
As my gift to you, buy one product and get a second one absolutely free.
So that secret URL, go to mrexcel.com/podcast916.html.
All lower case.
And that will give you a secret link to that secret bundle or one of those secret bundles-- either 2003 or 2007-- and pass along the podcast.
Again, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Oh, what, hey, is that a new iPod?
Did you get a new iPod for Christmas?
Wow, that's gorgeous.
Hey, thanks for watching the MrExcel netcast on your new iPod.
Hey, do you happen to use twitter?
Are you a twitter person?
Let's try a little experiment here, I was at a Neil Young concert a few weeks ago, and I twittered that I was at the Neil Young concert, and then I went to search.twitter.com, and searched for a Neil Young and found like two or three other people who were at the same concert, twittering about the concert.
So it's the day after Christmas, I'm sorry if you're stuck working, didn't have a vacation day, so just go out to Twitter and Twitter that you're watching the MrExcel.com podcast.
Make sure that you put MrExcel.com podcast, and then come back a few hours later and go to search.twitter.com and search for MrExcel.com; we'll see who else on twitter is watching the podcast.
Maybe we can start to follow each other.
And at the very least, it's going to kill 10 minutes on the day after Christmas when we're stuck at work and no one else is in the office.
Let's switch back to Excel here.
Cool Excel trick-- this came in during one of my classes.
Someone said, "Hey, look, I have this sheet and I need to number these items." They didn't actually have text there, they had real items.
And so they would number the first one, and then build little formulas-- =1 plus the cell above me-- and copy that formula down throughout most of the spreadsheet, but wherever there was a heading they had to kind of alter the formula to say =1 plus that last number up there.
And the problem with this particular setup was that they frequently would delete items.
So it's kind of like a to-do list-- these were open issues and when they would delete, of course, we're getting reference errors throughout.
Just not a good thing at all.
So here's an alternate way to go.
We're going to take advantage of the fact that there are two count functions: The COUNT(A) function will count all of the cells that are non-blank-- so text and numbers; but the TRUECOUNT function only counts other numeric cells.
So I'm going to set up a formula here: =COUNT, and I'm going to go from (A&1 to A the cell right above me, so in this case, since I'm in A3, I'm going to use A2-- =COUNT(A$1:A$2) +1.
I only put one dollar sign there to say that we're always going to start from the top of the spreadsheet and countdown to the cell right below me, and +1 at the end.
And so in the very first cell, we're going to get a 1, and as I copy that down, it continues to count.
And what's really cool about this is that if we delete a row-- so Edit, Delete Row-- it automatically renumbers; it doesn't change to reference error.
So let's take a quick look at that function down here.
Basically, we're saying we're going to COUNT-- not COUNT(A), but COUNT-- from A$1:A12-- the cell right above us-- and add 1 to it, and it creates a nice cool way to count all the way through.
Hey, I want to thank you for watching the MrExcel podcast.
If you're here at this point, it's the day after Christmas.
Again, I'm sorry that you're working; I've got quite a deal for you.
If you didn't get any cool MrExcel stuff yesterday, I'm going to give you a secret URL here on the screen.
Go to this URL, and if you buy either the live lessons 2003, or live lessons 2007, that's a whole bunch of video tips, then I'm going to throw in-- today only-- the MrExcel podcast DVD.
So the first 630 Episodes on a single searchable DVD.
As my gift to you, buy one product and get a second one absolutely free.
So that secret URL, go to mrexcel.com/podcast916.html.
All lower case.
And that will give you a secret link to that secret bundle or one of those secret bundles-- either 2003 or 2007-- and pass along the podcast.
Again, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
