Copy cell data from one sheet to another based off cell vaule

[PacVII]

I have two sheets in one workbook. DataEntry and DataSheet.

One of my teams input data in rows on the DataSheet using column C as the account number, then column D as customer name.

My other team goes into the workbook and will use the DataEntry sheet to search for data by account number in cell F10. I have the information pulled over from the DataSheet by a simple VLOOKUP. I then have two additional cells that need updated back to the DataSheet on the same row that VLOOKUP pulled over from. Copy cell H13 and I13 into DataSheet columns K and L. I have not had any luck and searched all over. Anyone have any idea how this can be done? Not even sure where to start wit the VBA.

 

[Profoundly Oblivious]

Your task seems deceptively simple so I suspect your goal is more complex than you indicate. Returning the value of DataEntry!H13 and DataEntry!I13 to the relevant row of DataSheet!K:L as determined by a VLOOKUP on DataSheet!C:C can be done with a simple IF. But if you don't want DataSheet!K and DataSheet!L to remain dynamic. Meaning, if you want them to populate when there is a match and remain populated while a different account number is checked ... well, that's a different ballgame with a different solution, so at the risk of going off topic, I'll give you both.

Disclaimer: I've made assumptions about your workbook and working environment and typed the solutions directly in the Quick Reply box. I've not tested these in any way.

Excel formula solution:
Type into DataSheet!K2 then drag the cell right one column and down far enough to reach the end of your data.

=IF($C2='DataEntry'!$F$10,'DataEntry'!H$13,"")

VBA Solution:
Copy the text open the VBA editor and paste this in the DataEntry object module. I assume DataEntry!F10 is manually changed. If it is then this will update DataSheet every time a new account number is checked. However, if DataEntry!F10 is populated with a formula then this is not a good solution for you.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i as Long
   Dim DS as Worksheet
   If Target.Parent.Name = "DataEntry" Then
      Set DS = ActiveWorkbook.Worksheets("DataSheet")
      With DS
         For i = 2 to .UsedRange.Rows.Count
            If .Range("C" & i).Value2 = Target.Parent.Range("F10").Value2 Then
               .Range("K" & i).Value2 = Target.Parent.Range("H13").Value2
               .Range("L" & i).Value2 = Target.Parent.Range("I13").Value2
            End If
         Next
      End With
   End If
End Sub

 

[Profoundly Oblivious]

if DataEntry!F10 is populated with a formula then this is not a good solution for you.

Just read through the code and realized I didn't verify Target.Address .... that means this actually updates when ANY cell DataEntry is manually changed. Oops.

 

[Profoundly Oblivious]

I can't figure out how to edit my post ... is this possible?

 

[My Aswer Is This]

I can't figure out how to edit my post ... is this possible?

You can only edit post within 10 minutes of posting. After that time it's no longer possible

 

[PacVII]

Just read through the code and realized I didn't verify Target.Address .... that means this actually updates when ANY cell DataEntry is manually changed. Oops.

So yes I am trying to copy two of the cells from theDataEntry sheet (H13 and I13) over to columns K (H13) and L(I13) of theDataSheet using the account number from cell F10 of the DataEntry sheet tomatch up with column C that has the account number to have that data input intothe row.

I am getting an error on the target and not sure how to fixthis.

F10 on the DataEntry sheet that I use to try and match overto the DataSheet is not a formula the user inputs that into the sheet as theaccount number)

I thought this would be easy but I am just not getting it.

Thank you,

 

[Profoundly Oblivious]

You can only edit post within 10 minutes of posting. After that time it's no longer possible

Gotcha, thanks!

 

[PacVII]

I got it - thanks!

'If Target.Parent.Name = "DataEntry" Then
Set DS = ActiveWorkbook.Worksheets("DataSheet")
With DS
For i = 2 To .UsedRange.Rows.Count
If .Range("C" & i).Value2 = Worksheets("DataEntry").Range("F10").Value2 Then
.Range("K" & i).Value2 = Worksheets("DataEntry").Range("H13").Value2
.Range("L" & i).Value2 = Worksheets("DataEntry").Range("I13").Value2
End If
Next
End With
'End If