How can I link information between 2 Excel files?
 

In my business- I type many proposals in a day to my customers. I have the
proposal form as well as my address book saved as Excel files. Is there a way
to link my address book to my proposal so information such as address, phone
#, and etc are automatically imput?

Easily accomplished. Enter a formula in the desired cells on your proposal
that reads like this:

='[AddressBook.xls]Sheet1'!$B$3

Do this for each field and make sure to include the full path name to your
address book xls file.
(ie: c:\Microsoft\Excel\AddressBook.xls) in the formula.
--
Regards...


"Sarahbkelly" wrote:

In my business- I type many proposals in a day to my customers. I have the
proposal form as well as my address book saved as Excel files. Is there a way
to link my address book to my proposal so information such as address, phone
#, and etc are automatically imput?

Hi Sarabkelly. I'm assuming your address book is in Sheet 2 and your
proposal form is in Sheet 1. You can use the VLOOKUP function to populate
the address book information into your proposal. Assuming your Sheet 2
information is Column A = Company Name; Column B = Address; Column C =
Telephone, and so on. Please correct the cell references to fit you data,
but, for example, on Sheet 1, Cell A1, you could type the Company Name. Then
in A2, use the VLOOKUP function to insert this formula: VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,2,FALSE). This will yield the Address, because it is in the
second column to the right in sheet 2. VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,3,FALSE) would yield the Telephone Number.

Use the $'s in the formula to make your data range Absolute for ease of
copying.

A couple of important things with VLOOKUP. Put Column headings in Row 1 on
Sheet 2: Company Name, Address, Telephone, and so on. The Company Name,
because you are using it as the ID for your lookup, must be unique. Also,
sort your address book on Company Name ascending. HTH
--
Sincerely, Michael Colvin


"Sarahbkelly" wrote:

In my business- I type many proposals in a day to my customers. I have the
proposal form as well as my address book saved as Excel files. Is there a way
to link my address book to my proposal so information such as address, phone
#, and etc are automatically imput?

Michael, (Sarabkelly):

=Sheet2!A1 (for each cell being lilnked)

....will accomplish the same thing without lengthy formulas. Essentially
this process links one cell with another either across the same workbook or
different xls files.

Assumming Sheet2 is the Address Tab and Sheet1 is the Proposal Tab these
links can be placed on the appropriate cells in the Proposal Sheet and will
mirror the reference cells in the Address Sheet.
--
Regards...


"Michael" wrote:

Hi Sarabkelly. I'm assuming your address book is in Sheet 2 and your
proposal form is in Sheet 1. You can use the VLOOKUP function to populate
the address book information into your proposal. Assuming your Sheet 2
information is Column A = Company Name; Column B = Address; Column C =
Telephone, and so on. Please correct the cell references to fit you data,
but, for example, on Sheet 1, Cell A1, you could type the Company Name. Then
in A2, use the VLOOKUP function to insert this formula: VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,2,FALSE). This will yield the Address, because it is in the
second column to the right in sheet 2. VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,3,FALSE) would yield the Telephone Number.

Use the $'s in the formula to make your data range Absolute for ease of
copying.

A couple of important things with VLOOKUP. Put Column headings in Row 1 on
Sheet 2: Company Name, Address, Telephone, and so on. The Company Name,
because you are using it as the ID for your lookup, must be unique. Also,
sort your address book on Company Name ascending. HTH
--
Sincerely, Michael Colvin


"Sarahbkelly" wrote:

In my business- I type many proposals in a day to my customers. I have the
proposal form as well as my address book saved as Excel files. Is there a way
to link my address book to my proposal so information such as address, phone
#, and etc are automatically imput?

I've having a really tough time with this one. My proposal is in sheet 1 and
address book in sheet 2. Address book is as follows: Column1= Customer Name,
C2=Address, C3= City, C4=State, C5=Zip, C6=Phone, C7=Fax
On the proposal form- Cell C8 is where I type the customer name, Cell C9 is
where I'd like Column 2 of the address book to populate (one column over from
the cooresponding customer name). I tried many formulas to make it work- one
of which was VLOOKUP(A1,'Sheet2'!$A$2:$C$100,2,FALSE). That returned an error
message. What I understand is this: Look up Value= C8, Table Array= Sheet 2,
Column Index #= 2, Range Value=False. This returns an error message for
'circular formula' which I don't understand.
I'm not sure which area of the formula is wrong. Please help!
Further- I'd like C10 to be (column3), (column4) (column5) {city, state
zip}- Cell I13 to equal column 6- Cell I14 to equal column 7

"Michael" wrote:

Hi Sarabkelly. I'm assuming your address book is in Sheet 2 and your
proposal form is in Sheet 1. You can use the VLOOKUP function to populate
the address book information into your proposal. Assuming your Sheet 2
information is Column A = Company Name; Column B = Address; Column C =
Telephone, and so on. Please correct the cell references to fit you data,
but, for example, on Sheet 1, Cell A1, you could type the Company Name. Then
in A2, use the VLOOKUP function to insert this formula: VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,2,FALSE). This will yield the Address, because it is in the
second column to the right in sheet 2. VLOOKUP(A1,'Sheet
2'!$A$2:$C$100,3,FALSE) would yield the Telephone Number.

Use the $'s in the formula to make your data range Absolute for ease of
copying.

A couple of important things with VLOOKUP. Put Column headings in Row 1 on
Sheet 2: Company Name, Address, Telephone, and so on. The Company Name,
because you are using it as the ID for your lookup, must be unique. Also,
sort your address book on Company Name ascending. HTH
--
Sincerely, Michael Colvin


"Sarahbkelly" wrote:

In my business- I type many proposals in a day to my customers. I have the
proposal form as well as my address book saved as Excel files. Is there a way
to link my address book to my proposal so information such as address, phone
#, and etc are automatically imput?