Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
If I link two files save and close both then just open 1 of those, it
doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
Have you tried:
In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to other documents In the other versions of Excel it will probably be under the File menu or Tools menu. "Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
Yes - 'Updatelinks to other documents' is ticked
"Normek" wrote: Have you tried: In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to other documents In the other versions of Excel it will probably be under the File menu or Tools menu. "Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
Yes - 'Updatelinks to other documents' is ticked
"Normek" wrote: Have you tried: In Excel 2007: Office Button - Excel Options - Advanced - Updatelinks to other documents In the other versions of Excel it will probably be under the File menu or Tools menu. "Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
I think you have to use Copy then Paste Link to get it to automatically update.
"Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
Hi Normek. Thanks for the reply.
Its showing an error message that Excel cant complete this task with available resources. Then showing unable to paste link Even if this had worked, do I have to use this for each file I will link to other files? Does it matter if the files are password protected? "Normek" wrote: I think you have to use Copy then Paste Link to get it to automatically update. "Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link 2 Excel files
You do need to unprotect your worksheets/workbooks to Paste Link. But once
the link is established it will update even if the cell is locked and the worksheet and/or worksheet is protected. Yes each Link in another workbook has to be established seperately, unless the source cell is accessed multiple times, in which case you can use the = operator to a linked cell in the target workbook. Lookup and sumif formulas are a bit more difficult. Try building them in a textbox and pasting them into Formula bar. "Salman" wrote: If I link two files save and close both then just open 1 of those, it doesnt pick up the links/formulas and shows #VALUE! for each cell, until the other one is open too. I manually linked them ie open 2 files; in one cell type = then go to the other file and link accordingly or vlookup, sumif etc to another excel file. On the same note sometimes I link the entire file or tab from the same file (ie ctrl+K then select the file or tab) but often that also fails to reopen. First I thought its because I am linking different Excel files ie Excel 97-2003 with xls (Excel 2007). Or xls with macro to xls without macro etc. But even I tried linking the same files eg xlsm with xlsm still the same problem. If I open the source file, it corrects the formula in the other file but if I only open the linked file (while source file is not opened) it shows #VALUE!. Is there any way to get rid of this error without even opening the source file otherwise I will have to open and close alot of files before I start work on any Excel file as most of my files are linked to many other files. At present its taking alot of time to open all other files, save and then close those to get rid of #VALUE! errors. I was wondering if there is any other way to get rid of these errors. For future reference - do you reckon linking different excel files could have caused this problem? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link to Excel files | Excel Discussion (Misc queries) | |||
link files into excel | Excel Discussion (Misc queries) | |||
CHK file in excel link files | Excel Worksheet Functions | |||
How can I link information between 2 Excel files? | Excel Discussion (Misc queries) | |||
how do i link 2 excel files? | New Users to Excel |