Using a formula to transfer information between sheets, in different rows

**SKAL** · 02-19-2009, 03:29 PM

I've attached a sample workbook.

I have a list of names with some background information. Three sheets, each representing a different time period and different test score.

On the first sheet, I have painstakingly collected the email address of each person. I've attached a few, but it's actually a few hundred.

On the second sheet, I have a few email addresses, but not all of them. However, there is some overlap in the names, so I may have already looked up the email addresses for some of the names in Sheet 1. So as you notice, Jet Li is already on Sheet 1, in Row 6. I would like to arrange it so that in Sheet 2, *if* the name is already in Sheet one, insert the email address. Note that on Sheet 2, Jet Li is on Row 11.

I also have Sheet 3, which may have some overlap with the first two sheets. I want to insert all the email addresses, if available, into the third sheet. So note that on Sheet 3, Jet Li is again there in Row 11. So is Tanya walker, who was in Sheet 1, and Vince Brood, from Sheet 2.

Note that names are in different rows. That's what I'm having trouble getting over, since the IF formula works if it's in the same row.

**mrice** · 02-19-2009, 05:35 PM

I would suggest a different approach using an event driven macro.

Open the VBA editor (Alt F11), select the ThisWorkbook tab in the Project explorer pane on the left and paste in the following.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Dim Cell As Range
Dim Sheet As Worksheet
Dim M As Integer
For Each Cell In Target
    If Cell.Column = 1 Then
        For Each Sheet In ThisWorkbook.Sheets
            If Sheet.Name <> Target.Parent.Name Then
                For M = 2 To Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row
                    If Cell = Sheet.Cells(M, 1) Then
                        If Sheet.Cells(M, 2) <> "" Then Cell.Offset(0, 1) = Sheet.Cells(M, 2)
                        If Sheet.Cells(M, 4) <> "" Then Cell.Offset(0, 3) = Sheet.Cells(M, 4)
                    End If
                Next M
            End If
        Next Sheet
    End If
Next Cell
Application.EnableEvents = True
End Sub

This will add both the e-mail and education values if they already exist on another sheet when an entry is made.

To update an existing sheet, simply select the names, copy and paste back upon themselves.

Hope this helps.

**Elmar** · 02-19-2009, 06:25 PM

Hi Skal,

I just received good advice with a problem I had (note the past tense ;o)

In your situation the use of VLOOKUP may be of some help and being immediate.

However, do keep in mind that people often change their e-mail addresses for any number of reasons, therefore, only use the newest one available. Same goes for their academic achievements or other pieces of info not shown on the sample workbook.

If the number of worksheets exceeds 3 or 4 it is probably better to go with the macro approach (much less typing :o)

Hope this doesn't throw a spanner into the works

Cheers

Elmar

**SKAL** · 02-19-2009, 06:58 PM

Thank you both.

I have to run out of work, but I will try these ideas first thing when I come in tomorrow.

**SKAL** · 02-19-2009, 06:59 PM

Oh, Elmar, I just changed the information for the sake of the example, since the actual info. I'm using is complicated. I'm not using students and email addresses - I'm doing something totally different.