Frank asks if there is a way to rewrite a formula using a scrollbar. While we can't do it with a scrollbar, I do have a way to rewrite formula with a dragging technique. Following along with Bill in Episode #1455 to see how it's done.
...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 1455: drag formula reference.
Well, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Francisco.
Francisco-- he actually-- he made me think about this.
He said, well, you know, why is it that we can't use a scrollbar to enter a formula?
He says, “I have this formula here that's counting the first range, but then if I have to count a second range, I have to retype that whole formula.
I just want to use a scrollbar.” I started to think about, well, how would you use a scrollbar?
Because you would have to use the scrollbar to specify like where it was going to start, but then there'd have to be a second scrollbar to figure out where it was going to end.
I said, you know, that's just not going to work, but there is a cool way to do this.
So, I have that first formula that's counting the red range and now, I want to count the yellow range.
First thing I want to do is Ctrl apostrophe.
Ctrl apostrophe will take the exact formula that's above and bring it down to this cell putting it in edit mode.
It's not truly in edit mode because I don't have the blue outline around the data.
So, I'm going to press Ctrl Enter to stay there and then either double-click or press F2.
All right, now, I'm in the edit mode that I want.
See this blue outline here?
That's A3 to F15.
That's the range that we're specifying.
You can drag that to the new spot.
So, I want to point to this yellow range.
Of course, it's a different shape, so I go to the lower right-hand corner and it's now taller and narrower and press Enter.
I did that formula.
I didn't have to retype that formula at all.
So, that's almost, I think, as good as a scrollbar.
So, thanks to Francisco for sending that question in.
Thanks to you for stopping by.
I'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1455: drag formula reference.
Well, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Francisco.
Francisco-- he actually-- he made me think about this.
He said, well, you know, why is it that we can't use a scrollbar to enter a formula?
He says, “I have this formula here that's counting the first range, but then if I have to count a second range, I have to retype that whole formula.
I just want to use a scrollbar.” I started to think about, well, how would you use a scrollbar?
Because you would have to use the scrollbar to specify like where it was going to start, but then there'd have to be a second scrollbar to figure out where it was going to end.
I said, you know, that's just not going to work, but there is a cool way to do this.
So, I have that first formula that's counting the red range and now, I want to count the yellow range.
First thing I want to do is Ctrl apostrophe.
Ctrl apostrophe will take the exact formula that's above and bring it down to this cell putting it in edit mode.
It's not truly in edit mode because I don't have the blue outline around the data.
So, I'm going to press Ctrl Enter to stay there and then either double-click or press F2.
All right, now, I'm in the edit mode that I want.
See this blue outline here?
That's A3 to F15.
That's the range that we're specifying.
You can drag that to the new spot.
So, I want to point to this yellow range.
Of course, it's a different shape, so I go to the lower right-hand corner and it's now taller and narrower and press Enter.
I did that formula.
I didn't have to retype that formula at all.
So, that's almost, I think, as good as a scrollbar.
So, thanks to Francisco for sending that question in.
Thanks to you for stopping by.
I'll see you next time for another netcast from MrExcel.
