to number a specified column of cells according to a value I've entered

**swordswinger710** · 02-21-2008, 02:13 PM

I need Excel to number a specified column of cells according to a value I entered in another cell.

Example One:

Cell A2 on Sheet 1 recieves the number 30.

Cells B3 to B33 on Sheet 2 should now have numbers 1 through 30 in them in ascending order.

Example Two:

Cell A2 on Sheet 1 recieves the number 17.

Cells B3 to B20 on Sheet 2 should now have numbers 1 through 17 in them in ascending order.

Is there a formula for this? I would really appreciate any response.

Thanks in advance.

**Dunc3142** · 02-21-2008, 03:00 PM

Let me know if this works for you.

**swordswinger710** · 02-21-2008, 03:58 PM

Thanks a lot! That works quite well! I guess my second question is this - how'd you do it? I did have something myself, but how do you enter the formulas into all those cells without having to manually edit the formula in each one, like I had to?

Here's my formula:

=IF(C2>0, (1), (""))
=IF(C2>1, (2), (""))
=IF(C2>2, (3), (""))
...and so on.

Now did you have to paste your formula into every cell like I did, and edit those two numbers before going to the next one? That, I suppose, is actually the question.

**Dunc3142** · 02-22-2008, 07:10 AM

When writing the formula, the $ before the C and before the 3 ($C$3) means that that part of the formula will not change when you copy it down.

=IF(A4="","",IF(A4<>$C$3,A4+1,""))

Copying the formula, I used the fill handle, (little black square in the bottom right corner of the active cell) to copy the formula down, mouse pointer changes into a small thin cross hair. When doing that the references that do not have $ in them will change to reflect new rows while the references that do have $ in them will not change.

The reason for the complicated formula was to keep #ERROR from being in the cells after the number was reached. The first if statement checks for the first cell with nothing in it and continues the entry after the first balck cell with more black cells. The second if statement will work by itself but when your number is reached you will start getting #ERROR in the cell.

**swordswinger710** · 02-22-2008, 11:05 AM

I always knew that $ would one day be good for something! So there is only one more issue - if C3 in your example is 0 or empty - cells A3 to infinity are now full of numbers. Is there a way for them to display nothing if nothing is entered?

**Dunc3142** · 02-22-2008, 07:42 PM

Put the following formula in cell A3

=IF(C3=0,"",C3-(C3-1))
That will fix it.

**swordswinger710** · 02-26-2008, 02:18 PM

Man, that was great! Thanks so much for all your input (and output). I do have several other Excel riddles that I need help with - if you're up to it. What do you say?