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!
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.....
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'?
LLLLL is
C:\Documents and Settings\User\My Documents\'
SSSSS is
Sheet1 or whatever your sheet (not workbook) is named
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?
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!
How would I set it up manually?
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.
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.
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks