A Challenge from none other than Bob Umlas - author of "Excel Outside The Box: Unbelievable Excel Techniques". Bob wants to Number Rows, but ignore the 'Hidden' Rows. In Episode #1476 Bill looks to a New Function for Excel 2010: " =AGGREGATE" Learn about it with the 'Learn Excel from MrExcel' Video Podcast.
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1476: number the visible rows.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
This-- I love this one.
This was actually a challenge sent out by Bob Ulmas.
Bob is an Excel MVP, author of that Excel Outside the Box book.
Bob was looking for a way to number rows, but it had to ignore the hidden rows.
So, we're not just looking for 1, 2, and so on because when we hide a row, what we're looking for is that to jump from 1 to 2 to 3.
I said, okay, here's what we're going to do.
We're going to use equal-- brand new in Excel 2010, AGGREGATE.
AGGREGATE.
This is a beautiful thing.
We're going to count, count, that'll count numeric and check this: ignore hidden rows.
Isn't that a great setting?
Ignore hidden rows and the range that we're going to use is B dollar sign 1 to B1 and we’ll say plus one because we want to add however many the previous ones there are.
Copy that down.
All right, so initially, we get the 1 2 3 4 5 6 7, but check this out.
When we hide rows-- format, row, hide, it will renumber.
Format, row, hide.
All right, so, it keeps updating and it counts only the visible rows above us.
Beautiful little trick there using Excel 2010.
Well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1476: number the visible rows.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
This-- I love this one.
This was actually a challenge sent out by Bob Ulmas.
Bob is an Excel MVP, author of that Excel Outside the Box book.
Bob was looking for a way to number rows, but it had to ignore the hidden rows.
So, we're not just looking for 1, 2, and so on because when we hide a row, what we're looking for is that to jump from 1 to 2 to 3.
I said, okay, here's what we're going to do.
We're going to use equal-- brand new in Excel 2010, AGGREGATE.
AGGREGATE.
This is a beautiful thing.
We're going to count, count, that'll count numeric and check this: ignore hidden rows.
Isn't that a great setting?
Ignore hidden rows and the range that we're going to use is B dollar sign 1 to B1 and we’ll say plus one because we want to add however many the previous ones there are.
Copy that down.
All right, so initially, we get the 1 2 3 4 5 6 7, but check this out.
When we hide rows-- format, row, hide, it will renumber.
Format, row, hide.
All right, so, it keeps updating and it counts only the visible rows above us.
Beautiful little trick there using Excel 2010.
Well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
