Data Entry to Update Next Empty Cell in Range

[tl17339]

[FONT=&quot]I am updating a list of closing prices each day. I would like to write a formula that posts the value entered in cell B2 to the next empty cell in the range C4-C10. Each prior day entry must be maintained in the list. I would appreciate any advice on how to write the Formula.
[/FONT][FONT=&quot]The range of values would not be reset after they are filled. Specifically this is for tracking a securities price, so the time frame could span beyond a year or two. It will provide the database for corresponding charts, moving averages. I tried to clip in a picture of what it would look like, but would not accept my snag-it program. I would appreciate your advice on how to write the formula for the destination cells C10 - C14. Thank-you Tom[/FONT]
[FONT=&quot]cell value[/FONT]
[FONT=&quot]b2 each daily value (closing price) is entered[/FONT]
[FONT=&quot]c4 Maintains 9/11 price entered[/FONT]
[FONT=&quot]c5 maintains 9/12 price entered[/FONT]
[FONT=&quot]c6 maintains 9/13 price entered[/FONT]
[FONT=&quot]c7 maintains 9/14 " "[/FONT]
[FONT=&quot]c8 " 9/15 " "[/FONT]
[FONT=&quot]c9 " 9/16 " "[/FONT]
[FONT=&quot]c10 " 9/17 " "[/FONT]

 

[My Aswer Is This]

I'm not a expert on Formulas.
But I could provide a vba solution.

But you said:

C4-C10

So this would be Ok for the first 7 days.
What would happen after day 7

You said:

I am updating a list of closing prices each day
so the time frame could span beyond a year or two​

How can we get prices for each day for more then a year entered into 7 cells.
​

 

[tl17339]

I apologize, and understand the confusion. To your point for a year it would be more like c4-c369. I would like to enter the closing price value in B2 each day, and have it populate the next "empty" cell down in the range (and maintain all prior days existing values). Thanks I hope this clarifies it. Appreciate you considering the solution.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a value in B2 the script will run.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/18/2018  10:34:09 PM  EDT
If Not Intersect(Target, Range("B2")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(Lastrow, 2).Value = Target.Value
Range("B2").Select
End If
End Sub

 

[tl17339]

Thank-you very much, I will work with this ASAP and respond with an update. Tom

 

[tl17339]

I just did it, works great!! Thank-you very much. Much appreciation - Tom

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

I just did it, works great!! Thank-you very much. Much appreciation - Tom

 

[My Aswer Is This]

Not sure if you wanted to be taken to new cell data at end of script. Or Be always taken to B2
My script keeps your active cell on B2

If you want changes let me know