Difficult formula problem: copy last entry (in the middle) and paste to another sheet in order

[diego_brazil]

Hi guys,

Do you know any formulas that solve the difficult problem below?

Action desired: "when an entry is made to columnA@sheet1, copy and paste it to the last blank cell of column A@sheet 2"

Problem: if entries of Sheet1 are made in order, this is easy. The complication comes when entries are 'in the middle' as below:

- Say that a user types in this order on Sheet1...

• 104 on cell A1
• 32 on cell A3
• 77 on cell A2 (entry 'in the middle' of cells A1 and A3)

- After each entry, I want it to be copied to the last blank cell of column A of Sheet2. Sheet2 should result in...

• 104 to cell A1
• 32 to cell A2 (since 32 was the 2nd entry, I want it on the 2nd cell of this column)
• 77 to cell A3 (since 77 was the 3rd entry, I want it on the 3rd cell of this column)

I am sure VBA can solve this, but I would love to see a formula if possible - if not possible please send your VBA suggestions!!

Thank you in advance,

Diego

 

[JoeMo]

Here's a VBA solution you can try. The code below is a module for Sheet1.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, nR As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
       For Each c In Intersect(Target, Range("A:A"))
              If Not IsEmpty(c) Then
              nR = IIf(IsEmpty(Sheets("Sheet2").Range("A1")), 1, _
                  Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1)
                 Sheets("Sheet2").Range("A" & nR) = c.Value
              End If
       Next c
End If
End Sub

 

[MarcelBeug]

Just to remark that a formula solution is not possible.

 

[Peter_SSs]

Welcome to the MrExcel board!

- Say that a user types in this order on Sheet1...

• 104 on cell A1
• 32 on cell A3
• 77 on cell A2 (entry 'in the middle' of cells A1 and A3)

- After each entry, I want it to be copied to the last blank cell of column A of Sheet2. Sheet2 should result in...

• 104 to cell A1
• 32 to cell A2 (since 32 was the 2nd entry, I want it on the 2nd cell of this column)
• 77 to cell A3 (since 77 was the 3rd entry, I want it on the 3rd cell of this column)

Let's suppose this has happened how you want, so at this point

Sheet1
A1: 104
A2: 77
A3: 32


Sheet2
A1: 104
A2: 32
A3: 77


What should Sheet2 look like if the user now goes to Sheet1 and alters A1 from 104 to 55?

What would Sheet2 look like if the user now Deletes the value altogether from Sheet1 cell A3?

 

[diego_brazil]

Worked Joe! Thank you so much!

 

[diego_brazil]

Hi Peter, thank you for your kind attention. Great push.

In the first case, can we write 55 on top of 104 on Sheet2?

And in the second case, can we delete 77 from Sheet2?

Another question: what if now I make the copy / paste action dependent on a binary value that is on column B?
Example:

User types 23 on cell A3 of Sheet1. Do nothing, because cell B3 is "0".
User types 55 on cell A4 of Sheet1. Do nothing, because cell B4 is "0".
User now changes B3 to "1". Then copy value 23 to the last empty cell of columanA@Sheet2 (action is only triggered when 0 becomes 1 on column B)
User now changes B4 to "1". Then copy value 55 to the last empty cell of columanA@Sheet2 (action is only triggered when 0 becomes 1 on column B)

Thank you again!

Best,

Diego

 

[Peter_SSs]

Hi Peter, thank you for your kind attention. Great push.

In the first case, can we write 55 on top of 104 on Sheet2?

And in the second case, can we delete 77 from Sheet2?

I don't see how we could do that. There are 2 difficulties:

1. Sheet2 is only recording the order things were put into Sheet1, it does not record what actual cells they were put into.

2. As soon as the 55 is entered into A1 of Sheet1, there is no record of what has just been typed over (104), so how would we even know what value to look for in Sheet2 to replace?