0

I'm not sure if this is possible, but I would like to create an ordered numeric list automatically based on a cell value. For example, if I have a cell with a value of 100, I want excel to automatically generate a list from 1-100. If instead that cell has a value of 50, I want excel to automatically generate a list from 1-50, starting in the same cell as before. Is this possible?

I would use this to help graph density functions (beta and binomial). If there is an easier way besides generating all the values in one column and using the pdf formula in the other column, please let me know.

Thanks

  • 2
    You might use the Fill - Series feature of excel to help. It's under the Home Tab near the AutoSum feature. – Jerry Jan 8 '14 at 16:20
  • I agree with @Jerry - it's probably easier that way. Or you could write a macro based on a cell, or it could prompt, or you could create a custom function that does it. There are a lot of ways to do it, but the easiest would be manually. – Raystafarian Jan 8 '14 at 17:02
  • Will look into it but I think my client would highly prefer it be automated. I usually do this kind of work in R or Python, not too sure on Excel's limitations. – TaylerJones Jan 8 '14 at 17:33
2

First enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, N As Long
    Set A = Range("A1")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        N = A.Value
        Range("A:A").Clear
        For i = 1 To N
            Cells(i, "A").Value = i
        Next i
    Application.EnableEvents = True
End Sub

Then enter a number in cell A1. Column A will automatically be filled with a sequence

  • Thanks, I will try this out and mark as answer if it works. – TaylerJones Jan 8 '14 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.