Excel rookie here…
How can I make my excel look up info from another file and automatically input into the new file.
eg: entering “client #” to get Address, Ph #, etc. automatically entered for that client from another file.
I tried the help file but can’t seem to locate to area I need.
Thanks in advance.
goto Help, select Contents and Index, select the Index tab, type in “links” (without the quotes).
Open both file A (source file) and file B (destination file, the file that looks up info from file A).
Enter a cell in file B. Type the equals sign. Click on file B to make it active. Select the Sheet containing the data you want to reference from the file B workbook. Select the cell or range of cells you want to reference. At the top of your screen in the formula bar you’ll see something like
!file A:Sheet1:A1
or, for that matter, if you were in mid-formula at the time,
sum(a1::a9/!fileA:sheet1:A1::A9
anyway, you can now click on the original file you were working in to make reference to other cells or ranges there, or hit return to enter the formula and return you to the sheet and workbook from which you started.
Note that Excel will have to open the source workbook A whenever you open workbook file B. If you want static (rather than dynamic) information from A and don’t want to have to have A open, copy the relevant cells in A, go to a sheet in B and Paste Special, selecting Results, which converts =!FileA:Sheet1:A1+1 to 2 (assuming A1 in Sheet 1 of File A is equal to 1) and no longer references file A at all. Now you can refer to the pasted results within file B in your file B formulas.
Designated Optional Signature at Bottom of Post
Sorry, that wasn’t quite accurate. When you refer to a cell in a different file (workbook A), it appears like this in the formula bar of file B:
=’[File A]Sheet 1’!$A$1
For a range of cells in file A, as in a formula, you’d see:
=sum(’[File A]Sheet 1’!$B$4:$B$18)
and if you want to paste the results in file B so you no longer need to have file A open all the time whenever you use file B, it is Paste Special then select Values, not Results.
Designated Optional Signature at Bottom of Post
Thanks
Much appreciated -
Buy ya a beer if i could.
Wishing all you at the SDMB Happy Xmas and a GREAT 2000 and beyond!
One last thought on this Smug, if you’re trying to build a database of accounts, and names, look at using a database program. Like Access, while it may be more difficult to set up initially, you’ll find that your data manipulation is easier, and that there is more room for growth.