Rafiqul wants to enter 10 in A1 and then have the numbers 1:10 appear starting in A2, A3, and so on. This podcast offers three solutions: a formula, a macro, and then, as a bonus, a cool trick with the fill handle and the Series dialog box.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1907.
Fill the N cells below A1 with the digits up to the value in A1.
Alright, well, now this is an interesting question sent in today from YouTube.
We're going to put the number 10 in cell A1 and then this 10 will distribute from cell A2 to A11.
Basically I want to distribute a cell value to the same number of cells.
Now, if I understand this correctly: so we're going to put a number here, 10.
And I'm just going to feed… start off with a number here of 1, but then the formula in cell A3 is going to be this: =IF(A2<A$1,1+A2,””).
It's going to say: if the number just above us is less than the item in row 1, so A$1, no dollar sign before the A, then I want to take 1 plus the number just above us.
Otherwise, I want “”, alright, and watch how this works.
I'm going to copy down not just ten rows, but I'm going to copy down, you know, a 100 rows or something like that.
And so, now this is pretty cool, check this out, so when I change the number in A1 from 10 to 15, I instantly get a different set of numbers.
I'm not sure, you know, exactly, what we're trying to do with this, but it's an interesting way to kind of expand a series or a list based on the number that's up here.
Now, the downside to this is, we have all of these extra cells that, you know, have formulas.
And I'm like, right here in my case, you know, 465 extra rows, that for the most part are never filled in, but we have to make it really large in case we ever, for example, will type 125 and would need some large number there.
So, you know, the formula is one way to go, another way to go would be a macro.
In here I'm going to show you how the macro works.
First, I'm going to say Expand Numbers, click Run and then that will take those numbers and change the list below.
But it's a static kind of thing.
If this would change from 10 to 12, see, it doesn't automatically update, you'd have to have an event handler macro to do that.
So we'll take a look at that code, quickly here, just so you know.
For Each cell In Selection.
So I selected those three cells.
For i = 1 To cell.Value; Cell.Offset(i, 0).
.Value = i.
Offset says: hey we're going to start from the individual cell, go down some number of rows, in this case 1, 2, 3, 4, 5, 6, 7, and set the value equal to the same number 1 through 10.
So tiny little macro there to handle that.
And, you know what, we're talking about filling.
You know, I just want to show off this this one cool trick.
I'm sure I've showed this on the podcast before, this came from Western Michigan.
If I would need to fill the numbers from 1 to, let's say a 100 000, you know, you can hold down the Ctrl key and start to drag.
But that, frankly, would take forever to get to a 100 000.
So I'm going to right-click and I'm going to drag down, and then back up, and let go.
And choose Series… from the drop-down list.
And then in the Fill Series I'm going to say, that I want to fill a Series in Columns.
The Step value is 1 and the stop value, let's just say it's 150 000, some really big number, click OK.
It's just bam, like that, check that out, very quickly fills down.
So that whole little dance at the beginning, where I put in the number 1 and grab the fill handle with the right mouse button, drag down and let go, is really just a shortcut way of coming here to the HOME tab and under Fill, choosing Fill Series.
But kind of a clever where to go.
Again, I got that from an accounting professor up at Western Michigan, I think it was University of Western Michigan.
So kind of a cool trick there.
But, I think really what we're asking for here, is just a way that we can enter a number and have the list automatically update based on that number.
So I think my original answer there in column A, is what they were looking for, but it was kind of an ambiguous question, so a couple of different alternatives there to fill some numbers.
All right, 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 1907.
Fill the N cells below A1 with the digits up to the value in A1.
Alright, well, now this is an interesting question sent in today from YouTube.
We're going to put the number 10 in cell A1 and then this 10 will distribute from cell A2 to A11.
Basically I want to distribute a cell value to the same number of cells.
Now, if I understand this correctly: so we're going to put a number here, 10.
And I'm just going to feed… start off with a number here of 1, but then the formula in cell A3 is going to be this: =IF(A2<A$1,1+A2,””).
It's going to say: if the number just above us is less than the item in row 1, so A$1, no dollar sign before the A, then I want to take 1 plus the number just above us.
Otherwise, I want “”, alright, and watch how this works.
I'm going to copy down not just ten rows, but I'm going to copy down, you know, a 100 rows or something like that.
And so, now this is pretty cool, check this out, so when I change the number in A1 from 10 to 15, I instantly get a different set of numbers.
I'm not sure, you know, exactly, what we're trying to do with this, but it's an interesting way to kind of expand a series or a list based on the number that's up here.
Now, the downside to this is, we have all of these extra cells that, you know, have formulas.
And I'm like, right here in my case, you know, 465 extra rows, that for the most part are never filled in, but we have to make it really large in case we ever, for example, will type 125 and would need some large number there.
So, you know, the formula is one way to go, another way to go would be a macro.
In here I'm going to show you how the macro works.
First, I'm going to say Expand Numbers, click Run and then that will take those numbers and change the list below.
But it's a static kind of thing.
If this would change from 10 to 12, see, it doesn't automatically update, you'd have to have an event handler macro to do that.
So we'll take a look at that code, quickly here, just so you know.
For Each cell In Selection.
So I selected those three cells.
For i = 1 To cell.Value; Cell.Offset(i, 0).
.Value = i.
Offset says: hey we're going to start from the individual cell, go down some number of rows, in this case 1, 2, 3, 4, 5, 6, 7, and set the value equal to the same number 1 through 10.
So tiny little macro there to handle that.
And, you know what, we're talking about filling.
You know, I just want to show off this this one cool trick.
I'm sure I've showed this on the podcast before, this came from Western Michigan.
If I would need to fill the numbers from 1 to, let's say a 100 000, you know, you can hold down the Ctrl key and start to drag.
But that, frankly, would take forever to get to a 100 000.
So I'm going to right-click and I'm going to drag down, and then back up, and let go.
And choose Series… from the drop-down list.
And then in the Fill Series I'm going to say, that I want to fill a Series in Columns.
The Step value is 1 and the stop value, let's just say it's 150 000, some really big number, click OK.
It's just bam, like that, check that out, very quickly fills down.
So that whole little dance at the beginning, where I put in the number 1 and grab the fill handle with the right mouse button, drag down and let go, is really just a shortcut way of coming here to the HOME tab and under Fill, choosing Fill Series.
But kind of a clever where to go.
Again, I got that from an accounting professor up at Western Michigan, I think it was University of Western Michigan.
So kind of a cool trick there.
But, I think really what we're asking for here, is just a way that we can enter a number and have the list automatically update based on that number.
So I think my original answer there in column A, is what they were looking for, but it was kind of an ambiguous question, so a couple of different alternatives there to fill some numbers.
All right, well, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
