need help either vba or formula?

[dani1]

i have 2 sheets 1st is entry and other is data sheet, pasted here n explained my question in 1st sheet is there a way to solve dis problem either using vba or excel formula?


Entry

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 153px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: left;" colspan="8" rowspan="5">This is the input form and I am gonna use it to enter data in the next sheet, keeping SR# as the reference. But the problem is that as I change the SR# to make the next data entry the previous entry gets updated as well. I don’t know any formula or VB thing to make previous entry permanent so that even if i change data in the input cell again the the previous entry doesn't get updated. vlookup , sumif, any looking up formula doesnt help.</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(51, 204, 204);">Input cells</td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 7px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(192, 192, 192);">Sr#</td> <td style="text-align: right;">1</td> <td style="background-color: rgb(192, 192, 192);">Name</td> <td>Jhon</td> <td style="background-color: rgb(192, 192, 192);">Telephone number</td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="text-align: right;">+129292929292</td></tr></tbody></table>
Data

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 92px;"> <col style="width: 12px;"> <col style="width: 145px;"> <col style="width: 12px;"> <col style="width: 145px;"> <col style="width: 12px;"> <col style="width: 145px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Default</td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Default</td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(192, 192, 192);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Date of entry </td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Sr#</td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Telephone</td> <td style="background-color: rgb(192, 192, 192);"> </td> <td style="background-color: rgb(128, 128, 128); text-align: center;">Name</td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: right;">1</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: right;">2</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: right;">3</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: right;">4</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150); text-align: right;">5</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr> <tr style="height: 19px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td> <td style="background-color: rgb(150, 150, 150);"> </td></tr></tbody></table>

 

[Ralajer]

A formula wouldn't work in this case so here is a VBA sub that should do what you need. You'll have to decided how to call it the easiest methods would be to assign it either to a keyboard shortcut or to command button next to the input fields. Let me know if it works out for you.

Sub CopyOverInput()
    Dim inputRng As Range
    Dim outputRng As Range
    Dim FFR As Long
    Dim valWarn As String
    
    Set inputRng = Sheets("Entry").Range("A10")
    
    With inputRng
    
    '// Input Data completeness validation
    If .Offset(0, 3).Value = "" Then valWarn = vbCr & " • Sr#"
    If .Offset(0, 5).Value = "" Then valWarn = valWarn & vbCr & " • Name"
    If .Offset(0, 8).Value = "" Then _
                        valWarn = valWarn & vbCr & " • Telephone Number"
    End With '// inputRng
    
    If Len(valWarn) <> 0 Then
        valWarn = "The following Field(s) are missing" & valWarn & vbCr _
                    & vbCr & "Do you wish to proceed?"
        valWarn = MsgBox(valWarn, vbYesNo, "Incomplete input")
        If valWarn <> vbYes Then Exit Sub
    End If
    
    With Sheets("Data")
        FFR = .Cells(Rows.Count, 2).End(xlUp).Row + 1
        Set outputRng = .Cells(FFR, 1)
    End With '// Sheets("Data")
    
    '// Input on D, F & I — Corresponding output D, H, F
    '// All are defined by offsets from column A
    
    '// Sr# (D to D)
    outputRng.Offset(0, 3).Value = inputRng.Offset(0, 3).Value
    
    '// Name (F to H)
    outputRng.Offset(0, 7).Value = inputRng.Offset(0, 5).Value
    
    '// Telephone (I to F)
    outputRng.Offset(0, 5).Value = inputRng.Offset(0, 8).Value
    
    '// Entry Date (B)
    outputRng.Offset(0, 1).Value = Date

End Sub