|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
| Notices |
 |
|
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
03-04-2007, 09:50 PM
|
|||
|
|||
|
Importing data from another excel file automatically
I am trying to have the total (cell e78) from one of my own excel files automatically filled into a cell in a different excel file when the number of that file is typed into a third cell.
i.e. if the total on Ticket 4126 in cell e78 is $4500, then when I type 4126 into the Ticket # cell on my seperate Invoice I'd like it to fill in the third cell under Amount as $4500. Is this possible? Thanks in advance for any help! 
|
|
#2
03-04-2007, 11:15 PM
|
|||
|
|||
|
try this
=INDIRECT("LLLLL'["&C5&".xls]SSSSS'!CCC") where LLLLL is the file location (drive and directory) C5 is the cell where I put the file name SSSSS is the worksheet name of the file in question CCC is the cell desired
__________________
not a professional, just trying to assist.....
|
|
#3
03-05-2007, 08:10 PM
|
|||
|
|||
|
OK, now to show my complete ignorance, for the 'LLLLL' what format does that need to be in? The location is C:\Documents and Settings\User\My Documents-would that be exactly what I put in place of 'LLLLL'? Also, what about the worksheet title, the 'SSSSS'?
|
|
#5
03-05-2007, 10:21 PM
|
|||
|
|||
|
Thanks for your help so far Duane!
I tried =INDIRECT("C:\Documents and Settings\User\My Documents'["&E22&".xls]Sheet1'!E78") Where E22 is where I enter the Ticket number (and I did a trial # that does exist) and E78 is the total from that ticket but it gives me '#REF!'-any suggestions?
|
|
#6
03-05-2007, 10:43 PM
|
|||
|
|||
|
you need a \ after documents but no '
I mistyped earlier on the ' to see the correct link set it up manually and then close the source workbook abd you will see where all the \, [], and ' are!
__________________
not a professional, just trying to assist.....
|
|
#8
03-06-2007, 10:52 PM
|
|||
|
|||
|
open the source file, and in the destination file in an empty cell type =, and go to the source file, select the right cell and hit enter.
The close the source file and see what the formula looks like.
__________________
not a professional, just trying to assist.....
|
|
#9
03-07-2007, 11:35 PM
|
|||
|
|||
|
When I do it manually it gives me
=[4126.xls]Sheet1!$E$78 I still can't get it to let me do it 'automatically' when I fill in the '4126' in E22. I hate to keep hounding you on this but I really want it to work. Is there any way you could set up a working example on your end and send me the resulting formula. I believe there is just a quotation out of place or a wrong symbol in the formula somewhere.
|
|
#10
03-07-2007, 11:54 PM
|
|||
|
|||
|
Played with it some more and this is what it gave me when I closed the source file
='C:\Documents and Settings\User\My Documents\[4126.xls]Sheet1'!$E$78 How do I get the '4126' to come from what I input into cell E22? I tried the "&E22&" that you suggested earlier and it gave me the dreaded #REF! again.
|
|
#11
03-08-2007, 12:11 AM
|
|||
|
|||
|
If you close the 4126.xls workbook you will get the full equation
Here is an example =INDIRECT("'C:\Documents and Settings\My Documents\xl\["&D8&"]2003 Refinance'!g46") where in cell D8 I place the name of the file (4126 in your case) to link to cell g46 on the worksheet 2003 Refinance Note the double quote after the (, after the [, before the ], and before the ) with a single quote before the C: and before the ! You do get a #REF if the source workbook is not open (at least in the version of excel I have
__________________
not a professional, just trying to assist.....
|
|
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
