Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet in which I enter a value into cells F58 and F59 on a daily basis.

At F81 is the start of a column of cells containing the value of cell F59 for each given day.

    F   
58  390.74  Dynamic Data

81  366.07  Static Data
82  363.98  
83  362.17  
84  360.74  
85  364.25  
86  363.5   
87  362.93  
88  365.05  
89  365.79  
90  368.56  
91  368.89  
92  368.32  
93  364.1   
94  ??????  Next Empty Cell

Is there a way that the data value entered into cell F58 can be automatically added to the next empty cell in column F?

share|improve this question

2 Answers 2

You could write a simple excel macro to do this (search google to see existing ones) or just use some excel hot key shortcuts. + (the "end" key) followed by the "arrow down" key (or cursor key) This will jump to the end of a column space. Since there is space between f58 and the column below, you'll have to hit these keys twice. Then once at the bottom of the column, (while your finger is still on the arrow key) hit the arrow key once more to move to the blank space. Of course, before moving, copy the cell's value with ctrl-C and paste it in to the empty cell with ctrl-V.

The number of keystrokes to do this is more than evoking a custom macro, but more portable and gets your brain/fingers in the right zone to be an excel speed user.

share|improve this answer

To do this automatically, I believe you will need VBA. The way I suggest implementing this is to use the Worksheet_Change event. To do this, open the VBA editor in Excel by pressing Alt+F11. From there, find the worksheet you are editing in the list of Microsoft Excel Objects on the left-hand side of the window, and double click it.

enter image description here

Then paste the following code into the text box that opens to the right (as pictured above).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("F58")) Is Nothing Then
    Range("F81").End(xlDown).Offset(1, 0) = Range("F58").Value
End If
End Sub

This will work as long as you already have at least two items in the list starting at F81. This code will run every time you make a change on that specific worksheet. It will check to see if the change was made to F58. If not, it will do nothing; if so, it will append the value of F58 to the bottom of the list starting at F81.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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