Formula help?
 

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

Formula help?
 

On Sep 5, 11:53*pm, K wrote:
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

One way...

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

filled down as far as needed.

Ken Johnson

Formula help?
 

in each cell in A

=IF(ROW()$I$15,"",ROW())



"K" wrote:

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

Formula help?
 

A bit of a novel way to do it. Right click sheet tabview 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

Formula help?
 

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

"K" wrote:

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

Formula help?
 

Replace:
Range("A1:A65536").Value = ""

With:
Columns(1).ClearContents

"Kent Prokopy" wrote:

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

"K" wrote:

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

Formula help?
 

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

--
Rick (MVP - Excel)


"K" wrote in message
...
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