Populating addresses from Sheet 1 to Sheet 2

[jchrisphonte]

Hello Everyone,

I currently have a workbook with 2 sheets - Sheet 1 is company names and sheet 2 is contact names...

All companies on sheet1 have a unique identifying number which can also be found on the contacts sheet to differentiate which contacts belong to which companies...

Here's the problem...

I have to enter in thousands of contacts to sheet 2, I have the company id, company name, contact name, and jobtitle....yet the address fields are always going to be the same as the ones from the same company on sheet 1. What I want the excel sheet to do is once i enter a new row and enter id# 551 for example it will fill the address, city, state, zip, phone and fax numbers to be exactly as they are on sheet 1 for company 551.

Hope this makes sense

Is this even possibe??

Thank you again in advance for your help - I am a big fan of this community...
Heres a visual below:

Sheet 1 -Companies

Excel Workbook
	B	C	E	F	G	H	I	J	K	L
2	551	1794 Commodore Funds	400 Madison Avenue	Suite 9D	New York	NY	10017	(212) 651-0502	(212) 651-0500	(212) 651-0503
3	552	2480 Capital Partners, L.P.	316 Yankee Trace Drive		Centerville	OH	45458	(937) 238-0021		(937) 227-9364
4	553	27 Capital Management LLC	9 Lincoln Street		Weston	MA	02493	(781) 893-2715		(781) 893-2715
5	554	36 South Investment Managers Ltd	Prince's Wharf, 147 Quay Street	Suite 24	Auckland	New Zealand	1143	+64 (0)9 306 1897		+64 (0)9 302-2767
6	555	36ONE Asset Management (Pty) Ltd	6A Sandown Valley Crescent		Sandown	South Africa	02196	+27 (11) 722 7390		+27 (11) 722 7391
7	544	3A Alternative Asset Advisors (Syz & Co Bank)	Rue du Rhone, 30-909	Case Postale 5015	Geneva	Geneva	CH-1211	+41 22 819 9800		+41 22 819 0904
8	556	3C Asset Management	Bulevardi 10 A		Helsinki	Finland	00120	+358 9 3481 5190		+358 9 3481 5151
9	4	3G Capital Management LLC	800 Third Avenue	38th Floor	New York	NY	10022	(212) 893-6727		(212) 893-6728
Funds

Sheet 2 -Contacts

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1	Company ID	Management Company	Salutation	Surname	Forename	Job Title	Address	Floor	City	State	Zip
2	551	1794 Management L.P.	Ms.	Safranek	Barbara	Business Development	400 Madison Avenue, Suite 9D, New York, NY, 10017
3	551	1794 Management L.P.	Mr.	Cafiero	Yolanda	CFO	400 Madison Avenue, Suite 9D, New York, NY, 10017
4	551	1794 Management L.P.	Mr.	Webber	Jeffrey A.	Chairman, CEO	400 Madison Avenue, Suite 9D, New York, NY, 10017
5	551	1794 Management L.P.	Mr.	Gerard	Rupert	Investment Analyst	400 Madison Avenue, Suite 9D, New York, NY, 10017
6	551	1794 Management L.P.	Mr.	Leal	Oscar	Managing Director	400 Madison Avenue, Suite 9D, New York, NY, 10017
7	551	1794 Management L.P.	Mr.	Heller	Preston	Senior Analyst	400 Madison Avenue, Suite 9D, New York, NY, 10017
8	552	2480 Capital Holdings, LLC	Mr.	Giles	Thomas	Fund Manager	316 Yankee TraceCenterville, OH 45458
9	553	27 Capital Management LLC	Mr.	Okurowski	Lee	Fund Manager	9 Lincoln Street,Weston, MA 02493
10	553	27 Capital Management LLC	Mr.	Musumeci	Ross	Partner	9 Lincoln Street,Weston, MA 02493
11	554	36 South Investment Managers Ltd	Mr.	Pretorius	David	Marketing Manager	Prince's Wharf, 147 Quay Street, Auckland 1143, New Zealand
12	554	36 South Investment Managers Ltd	Mr.	Haworth	Richard	Principle/Associate	Prince's Wharf, 147 Quay Street, Auckland 1143, New Zealand
13	555	36ONE Asset Management (Pty) Ltd	Mr.	Jacobs	Cy	Fund Manager	6a Sandown Valley CrescentSandownSandton2196
14	555	36ONE Asset Management (Pty) Ltd	Mr.	Liptz	Steven	Fund Manager	6a Sandown Valley CrescentSandownSandton2196
15	555	36ONE Asset Management (Pty) Ltd	Ms.	De Abreau	Tanya	Fund Manager	6a Sandown Valley CrescentSandownSandton2196
16	555	36ONE Asset Management (Pty) Ltd	Mr.	Isaacs	Daniel	Management Consultant	6a Sandown Valley CrescentSandownSandton2196
17	555	36ONE Asset Management (Pty) Ltd	Ms.	Slatter	Laurie	Principle/Associate	6a Sandown Valley CrescentSandownSandton2196
18	555	36ONE Asset Management (Pty) Ltd	Mr.	Seaborn	Ryan	Principle/Associate	6a Sandown Valley CrescentSandownSandton2196
Contacts

Also keep in mind that most contacts do not have an address that can be split to columns like these examples


Jerry

 

[Bill Rockenbach]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

Are you going to enter every name in the Fund sheet into the Contact sheet or just selected funds??

 

[jchrisphonte]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

Yes - Every fund - they all have at least one contact

 

[Bill Rockenbach]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

If more than one contact howdose the second, third, etc contacts appear in the Funds list?

Any reason why the whole Funds list can not be added to the Contacts list all at once??

 

[jchrisphonte]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

Hey Bill,

For the second,third and beyond contacts the address fields will appear just as the first would.

Good question about using one sheet instead - unfortunately this is being fed into the back end of a website...all the columns have to be the same as they were when built

So yes, we do have to keep the format of two sheets.

 

[Bill Rockenbach]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

For the second,third and beyond contacts the address fields will appear just as the first would.

Does this mean the Fund number is repeated in column B for each contact for that fund? Can you post a sample of multiple contacts for a single fund?

Any reason why the whole Funds list can not be added to the Contacts list all at once??

What I'm asking is can't the Contacts sheet be updated by looping through the Funds sheet and copying each Fund to the Contacts sheet?

Did you want to copy which includes formats or just assign the values from the Funds sheet to the Contacts sheet?

 

[jchrisphonte]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

Hey Bill,

Yes - The Fund number is repeated in column B for each contact for that fund. I postedan image earlier to help... if you look at company 551 there are 6 contacts that all share the same company identifier.

i understand your question now, I would like to just assign the values from sheet 1 to sheet 2

 

[Bill Rockenbach]

Re: PoPopulating addresses from Sheet 1 to Sheet 2

Put this code in the Contacts sheet code module. IF you enter a correct Funds number in column B of the Contacts sheet the procedure will assign the address, etc. from the Funds sheet to the Contacts shhet

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngFunds
    Dim FundsRow As Long
    Dim ContactsCol As Integer
    If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    With Sheets("Funds")
        Set rngFunds = .Range("B1", .Range("B65536").End(xlUp))
        FundsRow = 0
        On Error Resume Next
        FundsRow = rngFunds.Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole).Row
        If FundsRow = 0 Then
            MsgBox Target.Value & " Fund does not exist."
            Exit Sub
        End If
        Application.EnableEvents = False
        For ContactsCol = 8 To 15
            Sheets("Contacts").Cells(Target.Row, ContactsCol) = .Cells(FundsRow, ContactsCol - 4)
        Next ContactsCol
        Application.EnableEvents = True
    End With
End Sub

If you have the Contact names already in a worksheet then I suggest a loop to assign all the addresses, etc. Let me know and I will give provide code for a loop.