Formula

[Jtandme]

I am needing a formula that will check cell a3 if has data go down to next cell, then copy the data from another worksheet called invoice and the cell is a13.

[#2]

@Jtandme 

I do not think this can be done with a formula, because a conditional formula returns a value but not a selection.

However it could be done by a code in VBA. Also you need to decide how would you like to run that code: Is it manually? or with the opening event of the workbook or with the change happening to a cell you specify?

I need more details or better a sample file to do it properly.

The concept is as follows

ALT + F11 >> opens the VB editor

ALT + I + M >> creates a module

then type:

Sub Evaluate()

   If Range("A3"). value <> "" Then

      Range("A4"). select

      ActiveCell.value= Sheets("Invoice").Range("A13").value

   End If

End Sub

Hope that helps

Nabil Mourad

[#3]

@Jtandme 

Or place this formula in A4

=IF(A3="","",Invoice!A13)

[#4]

@Jtandme 

Hi again

In my answer I focused on "SELECTING" the cell below assuming that you want this cell to be selected.

However if "Selecting A4" is not a must then, without a doubt go with the nice formula provided by our team mate @Subodh_Tiwari_sktneer 

Thank you

Nabil Mourad

[#5]

I need the code to run when the data changes in the invoice sheet a13 so to copy the info to a tracking sheet.

[#6]

Also reasoning for checking cell a3 was so not to over write the data. I need the code to check the last cell in the column with data then move down to next vacant cell and Copy data from invoice sheet a13 into the black cell. Sorry for the confusion.

[#7]

@Jtandme 

Hi

To run the code whenever A13 changes in the Invoice worksheet, then

You need to attach the code to cell A13 Change event:

To do that,

Right click the worksheet Tab (Invoice) >> select view code

In the VB Editor, you have 2 drop lists, from the left one select "Worksheet" from the Right one select "Change"

Then write the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A13").Address Then
Call Evaluate
End If
End Sub

If you want to check if a cell is empty then proceed to the cell below without overwriting, that requires modifying the Evaluate code and let it loop before copying.

[#8 · Accepted solution]

@Jtandme 

Based on your description, assuming you have two sheets called "Tracking" and "Invoice" and if you want to copy the cell A13 on Invoice Sheet if changed to the first empty cell after the last row with data in column A on Tracking Sheet, place the following code on the Invoice Sheet Module.

To do so, right click on Invoice Sheet Tab --> View Code and paste the code given below into the opened code window and save your workbook as Macro-Enabled Workbook.

If name of your tracking sheet is not "Tracking", change the name of tracking sheet it in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsDest  As Worksheet

Set wsDest = Worksheets("Tracking")     'Assuming Tracking is the name of the Tracking Sheet, change it as required

If Target.Address(0, 0) = "A13" Then
    If Target <> "" Then
        Target.Copy wsDest.Range("A" & Rows.Count).End(3)(2)
    End If
End If
End Sub

Let me know if this is what you were trying to achieve.

[#9]

I am not a vb person how do I make it loop? I gather I write the code on the worksheet side and then the change side I with the second list of code you typed. Is this correct? Thank you for all this help.

[#10]

That worked perfect, my next thing is when it copies invoice sheet a13 to tracking sheet column a is there a way it can copy the data from invoice sheet cells d4,d5,d36 to the next blank cells in tracking sheet columns c, d, e? I think that can be put into same macro just like a13 is. Not sure.

[#11]

Got everything working thank you

[#12]

@Jtandme 

You're welcome! Glad it worked as desired.

Please take a minute to accept the post with proposed solution which resolved the issue as an Answer to mark your question as Solved.

[#13]

I am new here how do I accept as solved?

[#14]

@Jtandme 

Same here. I have never asked any question here so not aware of how to accept a post as an answer.

But it seems you will find a button called Mark as Best Response next to Like button under a post.

Refer to the following image and see if that's the case. :)

[#15]

So I thought everything was working fine and the first issue is but when I put a formula in cell c2,d2 and e2 it copies the data form the invoice sheet. But when I input a new acct # it updates c2 row and the c3 row. I do not want it to update the previous rows because that messes up the tracking info. How can I do this?

[#16]

@Jtandme 

Can you please share the Change Event Code you finally have on Sheet Module?

And then explain what it does and what do you expect it to do actually?

[#17]

All I have added is the last code you sent me to add to the invoice sheet and it works fine. But I need someway to copy 4 other fields to the tracking sheet like I referenced earlier to you with out changing the data In The previous rows to match this current data.

[#18]

@Jtandme 

See if this is what you are trying to achieve. Pay attention to the comments added in the code and tweak it as per your requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsDest  As Worksheet

Set wsDest = Worksheets("Tracking")     'Assuming Tracking is the name of the Tracking Sheet, change it as required

'Changing A13 on Invoice sheet will copy A13 to the Tracking in the first empty cell after the last row with data in Column A
If Target.Address(0, 0) = "A13" Then
    If Target <> "" Then
        Target.Copy wsDest.Range("A" & Rows.Count).End(3)(2)
    End If

'Changing C2 on Invoice sheet will copy C2 to the Tracking in the first empty cell after the last row with data in Column C
ElseIf Target.Address(0, 0) = "C2" Then
    If Target <> "" Then
        Target.Copy wsDest.Range("C" & Rows.Count).End(3)(2)
    End If

'Changing D2 on Invoice sheet will copy D2 to the Tracking in the first empty cell after the last row with data in Column D
ElseIf Target.Address(0, 0) = "D2" Then
    If Target <> "" Then
        Target.Copy wsDest.Range("D" & Rows.Count).End(3)(2)
    End If

'Changing E2 on Invoice sheet will copy E2 to the Tracking in the first empty cell after the last row with data in Column E
ElseIf Target.Address(0, 0) = "E2" Then
    If Target <> "" Then
        Target.Copy wsDest.Range("E" & Rows.Count).End(3)(2)
    End If
    
End If
End Sub

[#19]

Following is code I have seems all is working except the D35 cell is not copying the the tracking sheet D column. All the others do. Any idea why?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsDest As Worksheet

Set wsDest = Worksheets("Tracking") 'Assuming Tracking is the name of the Tracking Sheet, change it as required

If Target.Address(0, 0) = "A13" Then
If Target <> "" Then
Target.Copy wsDest.Range("A" & Rows.Count).End(3)(2)
End If

ElseIf Target.Address(0, 0) = "D4" Then
If Target <> " " Then
Target.Copy wsDest.Range("C" & Rows.Count).End(3)(2)
End If

ElseIf Target.Address(0, 0) = "D35" Then
If Target <> " " Then
Target.Copy wsDest.Range("D" & Rows.Count).End(3)(2)
End If

ElseIf Target.Address(0, 0) = "D5" Then
If Target <> " " Then
Target.Copy wsDest.Range("E" & Rows.Count).End(3)(2)
End If

End If
End Sub

[#20]

@Jtandme 

That's difficult to answer why it isn't working. Do you change D35 manually? The point to remember here is that the Change Event Code will only be triggered automatically when you change the content of the cells manually not if the content is changed through formulas.