Formula help?

K

K

Good day.

How do I take a cell (I15)'s result for example say it is 10 and have column
A put 1 to 10 from A1 - A10 or in the respected fields? And if the result
changes to 20 say it fills in the cells from A1 - A20 with 1,2,3, etc. to 20?
is this possible?

Kyle
 
K

Ken Johnson

Good day.

How do I take a cell (I15)'s result for example say it is 10 and have column
A put 1 to 10 from A1 - A10 or in the respected fields? And if the result
changes to 20 say it fills in the cells from A1 - A20 with 1,2,3, etc. to20?
is this possible?

Kyle
Click to expand...

One way...

=IF($I$15-ROW(1:1)>=0,ROW(1:1),"")

filled down as far as needed.

Ken Johnson
 
D

Don Guillett

A bit of a novel way to do it. Right click sheet tab>view code copy/paste
this.
Be advised that this will fire with any and all calculations.

Private Sub Worksheet_Calculate()
Columns(1).ClearContents
Application.EnableEvents = False
Range("a1:a" & Range("b1")).Formula = "=Row(a1)"
Columns(1).Value = Columns(1).Value
Application.EnableEvents = True
End Sub
 
K

Kent Prokopy

This is a little more dynamic.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x As Long
If Target.Address = "$I$1" Then
Range("A1:A65536").Value = ""
For x = 1 To Target.Value
Range(Cells(x, 1).Address).Value = x
Next x
End If
End Sub
 
R

Rick Rothstein

This Change event code for the worksheet you want to have this functionality
will automatically change the numbering in Column A whenever the number in
I15 is changed...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
If Target.Address = "$I$15" Then
Columns("A").Clear
For X = 1 To Target.Value
Cells(X, "A") = X
Next
End If
End Sub
 
Top