Excel question

Factual Questions
1

I have a sheet where I update a number in (say) cell A1 once a week.

On another sheet I would like that number to be auto-input in cell A1, then the following week put the new number (updated on the first sheet) in cell A2, following week cell A3 (and so on).

Hope that makes sense. I want to keep a running record of the number I update over time on the other sheet. So basically I want Excel to create a list each time I update the one cell on the first sheet.

Any way to do this?

2

I think you’ll find the “INDIRECT”, “ROW” and “COLUMN” functions will let you accomplish what you’re after.

3

The only way I can think of to do this is a VBA macro. It’s not terribly difficult, I would write one up, let me get back to you on that.

4

Can you explain that? What the OP is saying is that he enters a number in Sheet1!A1. The number magically shows up in Sheet2!A1. Then the next week he updates the value in Sheet1!A1. That number then magically shows up in Sheet2!A2. Then the next week he updates the value in Sheet1!A1. That number then magically shows up in Sheet2!A3. Ad infinitum.

5

In Excel, press ALT-F11. This opens up the Excel VBA development environment. On the left, double-click on Sheet1 (or whatever sheet you want to update A1).

Paste in this code:



Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = Range("A1").Address And Not IsEmpty(Target) Then
   
      Worksheets("Sheet2").Cells(nextAvailableRow(Worksheets("Sheet2")), 1).Value = Target.Value
   
   End If

End Sub

Public Function nextAvailableRow(w As Worksheet) As Long

   If IsEmpty(w.Cells(1, 1)) Then
      nextAvailableRow = 1
   Else
      nextAvailableRow = w.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   End If

End Function

If your second sheet, the destination, is named something different from “Sheet2” you must change the code to reflect that in the two places it occurs.

Make sure you allow macros to run.

When you update a cell in Sheet1, it will create an event called Change. The Sub above will trap the event. If the changed cell is A1, then it will copy that value to the next available row in Column A of Sheet2.

6

You, sir, are a prince!

I will try this when I get home.

Thanks a million!

7

When it doesn’t work send me an email :slight_smile: