Formula to copy phone and fax from Sheet 1 to sheet 2

J

jchrisphonte

New Member
Joined
Jun 21, 2009
Messages
37
Hello Everyone,

I have an excel workbook with two sheets... On the first sheet is company names and on the second is contact names... In column c of both sheets is a unique company identifier number which links the contacts on sheet 2 to the appropriate company on sheet 1...

Here's what i need a formula to do...
For some...not all contacts on sheet 2 there are no phone(column o) or fax (column p) numbers listed yet on sheet 1 with the companies there is usually the main office number(column J) and main fax (column L)...


I'd like to sort the contacts sheet by phone and drag a formula that will first look at the unique company identifier in column c and then fill the phone with the one from sheet 1... same for fax -use company identifier in column c and fill in blank field with company fax found on sheet 1...

Here are some visuals to help....

Thank you in advance for all your help -It is truly appreciated.

Jerry

Sheet 1 first column is c

Excel Workbook
CDFGHIJKLM
24580Abria Alternative Investments Inc.20 Adelaide Street EastSuite 300TorontoONM5C 2T6(416) 367-4777(416) 367-4555
25547ABS Investment Management LLC55 Railroad Ave3rd FloorGreenwichCT06830(203) 618-3737(203) 622-4889
26582Absa CapitalWillie van Schoor Drive2nd Floor Tijgerpark IVTygervalleySouth Africa021 915 5305021 914 0997
Funds


Sheet 2 (first column below is c)

Excel Workbook
ABCDEFG
1unique idCompanyName 1Name 2TitlePhoneFax
2580Abria Alternative Investments Inc.KneisHenryCEO416-367-4777416 367 4555
3580Abria Alternative Investments Inc.Dominic*StanisciaCFO
4580Abria Alternative Investments Inc.DomanAndrewChief Operating Officer416 367-4777416 367-4555
5580Abria Alternative Investments Inc.Scott*AndersonOperations Administrator
6580Abria Alternative Investments Inc.Susan*GreenVice President Business Development
7580Abria Alternative Investments Inc.Brian*KralikVP Finance
8547ABS Investment Management LLCDonald LeungAnalyst
9547ABS Investment Management LLCMichael*HalperAnalyst*
10547ABS Investment Management LLCOmar*YacoubAnalyst*
11547ABS Investment Management LLCChristian*ThornAnalyst*
12547ABS Investment Management LLCJeff*AllevaAnalyst*
13547ABS Investment Management LLCWhiteSeanCFA203 618 3787203 618 3717
14547ABS Investment Management LLCMauricio*JustoInvestment*Analysis
15547ABS Investment Management LLCDe CosterAlainSenior Executive001203-618-3700 1 #203 618 3768
16547ABS Investment Management LLCDeCosterAlainSenior Executive+1 212 816 4999
17547ABS Investment Management LLCCosterAlain DeSenior Executive203 618 3768
18547ABS Investment Management LLCHalperMikeSenior Executive
19547ABS Investment Management LLCDe CosterAlainSenior Executive203 622 4818203 622 4889
20547ABS Investment Management LLCMurphyMarkSenior Executive203-618-3737203-622-4889
21547ABS Investment Management LLCKimTaeSenior Executive203 618 3788203 618 3718
22547ABS Investment Management LLCValleGuilhermeSenior Executive
23547ABS Investment Management LLCRussianLaurenceSenior Executive203 618 3770/3700203 618 3720
24582Absa CapitalBrandtHermanSenior Executive
25582Absa CapitalBradyGizeldeSenior Executive
26582Absa CapitalOfongTemiSenior Executive
27582Absa CapitalBunkellAdamSenior Executive
Sheet1



(I wish i understood VBA enough to implement a macro yet if you think that may be he only way please explain in detail for an 07 excel novice)

Once again thanks for your help...
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
vlookup will do just what you need

Excel Workbook
ABCDEFG
1unique idCompanyName 1Name 2TitlePhoneFax
2580Abria Alternative Investments Inc.KneisHenryCEO(416) 367-4777(416) 367-4555
3580Abria Alternative Investments Inc.Dominic*StanisciaCFO
Sheet2
 
Upvote 0
Hi Lynn,

Thank you for the help... I'm having a bit of trouble making it work though
Would you mind explaining what the below represents and if it is from sheet 1 or sheet 2

A2
C24
L26
8


$A2,Sheet1!$C$24:$L$26,8,FALSE

Sorry for the bother - your example below is spot on.... just dying to make it work on my own sheet
 
Upvote 0
ok so on sheet two I put the formula starting in column F. So the A2 is the "580" in that cell. The second part points to the table on Sheet1 where the unique ID matching data begins in column C and includes the data through the fax column (L). The third part tells which column contains the data to return, and since the 1st column of this example is column C, that is 1, the columns count from that point onward, making column J the 8. The last part "FALSE", tells excel to make the match "580" in the first example to be an exact match. Read more in help about vlookup. Here is the sample data you gave that I used to create the formula

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 31px"><COL style="WIDTH: 164px"><COL style="WIDTH: 264px"><COL style="WIDTH: 89px"><COL style="WIDTH: 110px"><COL style="WIDTH: 46px"><COL style="WIDTH: 39px"><COL style="WIDTH: 112px"><COL style="WIDTH: 64px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">580</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Abria Alternative Investments Inc.</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">20 Adelaide Street East</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Suite 300</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Toronto</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">ON</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">M5C 2T6</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">(416) 367-4777</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc"> </TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">(416) 367-4555</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">547</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">ABS Investment Management LLC</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">55 Railroad Ave</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">3rd Floor</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Greenwich</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">CT</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">6830</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">(203) 618-3737</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc"> </TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">(203) 622-4889</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">582</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Absa Capital</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Willie van Schoor Drive</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">2nd Floor Tijgerpark IV</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">Tygervalley</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">South Africa</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc"> </TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">021 915 5305</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc"> </TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc">021 914 0997</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
You're a life saver Lynn!!!!

Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You

:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Copy range of data from multiple sheets & paste into single column of summary sheet
Replies
1
Views
497
Micron
M
E
  • Question
VBA to copy same range from multiple files onto multiple sheets in a summary file
Replies
4
Views
442
Elinda
E
B
  • Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
948
mumps
mumps
E
  • Solved
VBA macro - Match to a single cell, copy multiple hits (with rows) and paste in first available row in a different sheet
2
Replies
11
Views
1K
Alex Blakenburg
A
E
  • Solved
If value in a cell match any cell of a roll in a diferent sheet then copy a specific value from that same column
Replies
7
Views
306
Fluff
Fluff

Forum statistics

Threads
1,216,101
Messages
6,128,843
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top