Excel REF error when linked source file closed
Hi All,
looking for a resolution - I don't know why this is happening, but I'm using Thanks!! I've searched everywhere for a clear answer to this but I can't find any and it seems like such a simple problem: Let's say I have 6 sheets. The first is a summary page, the other 5 are named "Monday", "Tuesday" etc thru "Friday". I've set up these 5 day-of-the-week sheets with the same exact tables, only with different values in the cells from day to day. In A2 on the summary page I want to write a long, complicated formula where Monday occurs several times in calculating Monday's results. (i.e. =((Monday!A1*2)+(Monday!A2*3)/LN(2))-(Monday!A4/Monday!A7) etc etc...) To see Tuesday's results, I want Excel to change the data in that summary sheet's formula automatically so I don't have to delete Monday and type Tuesday 10 times. I'd rather type the word Tuesday into A1 and have the complicated formula reference A1 and know to change the text of Monday into Tuesday in all the instances in the formula. Does that make sense? It seems excel should have this function since it would save a lot of typing, especially if I have 100 different sheets.. Thanks! Dirk says: Hi, I'm using the latest Excel version via Office365, so I can use data ranges converted to tables. A second workbook is pulling data from the first one , also by using the x.lookup function and extracting data from the different sheets based on specific parameters. Both workbooks are stored on OneDrive in the same folder. It all works fine as long as the first workbook (the data container) is opened on my PC. When I close this file, The second workbook gives error messages as result for the x.lookup referring to the tables. It seems that the references in the x.lookup function are not recognized any more. Is this normal behaviour ? Can I extract data from (dynamic) tables in other workbooks when these files are closed ? I hope this can be done without the use of PowerQuery (which is also a great tool). Looking forward to your reply. ReplyJJ Lee says: Thanks for the reply Alexander, I'm trying to only pull the value of a cell on another workbook on the internet. Not a hyperlink to the other file. Hopefully this example makes sense. Any suggestions will be greatly appreciated! Nav says: We have an Excel Workbook with 10 to 15 worksheets and all these worksheets interacts with one another for calculations and for data. Some of these worksheets gets Data from other excel workbooks by a cell reference formula. The main workbook depends on worksheets and other excel files/workbooks to get data and do calculations this work is done once a year. So many references, formulas and calculation are involved. Each year we copy all the last year workbooks and update the source files to do the calculations for the present year. The master excel workbook mostly does the calculation by itself when the source data feeds into the workbook and there are some excel cells in the master workbook that needs data from user but this is rare. Is there a way to track all the changes happened in the source files that got feed into master workbook, like old values in source values that got updated to new values and also capture old and new value in master book for comparison. Tracking should capture values not formulas. ReplyNav says: Hello, thank you for quick response. I tried track changes within excel but this doesn't work in my situation. If i have an empty cell in master excel and if i have a formula that gets value from other excel file track change shows old value as 0 and new value as formula.But if i update source file with different value track change wont capture this because formula will be same. We have some where around 12 source excel files that we update value and this data will feed into master excel file I am new to last changed cell concept but i want to track each and every cell that got updated in source file with old value and new value. This data will feed into master workbooks and again i want to capture old and new value so we can just manually check the two log sheets to make sure all the data is correct and feed properly into master excel file. I am hoping there will be way and thanks for future response. ReplyAlexander Trifuntov (Ablebits Team) says: Hello Heather! Thus, if any additional actions are required from a user to recalculate the data in the source file, you won’t get the data from this file until the user opens it and allows to update the data or run macros. Most likely, the file from which you pull the data contains some external references as well. Unless the file is open, the references can’t be updated. Therefore, it is not possible to automatically recalculate the data. Besides, there may be macros in the source file that also require an additional permit to be run. While Excel is waiting for this action to take place, it doesn’t transfer the data from this file to other files. Why does my Excel keep saying reference not valid?This means that you do not have access to the Excel file locally, or lost access to it on a network drive or it you could have referenced a temporary file. Therefore, you may find this invalid references error when you open the pivot table file directly online or via the website.
Does indirect work with closed workbooks?An INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed. In this example, you'll create a formula with the INDIRECT function, using references to a file name, sheet name and cell name.
|