Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE! error in master when linked sheets aren't open?
Hi All,
I've run into a bit of a weird problem in a master workbook I'm creating. In essence, it's a roll-up of about 20 different multi-departmental metrics, and links to nearly as many sources. Out of all of these sources, two of them are CSV files converted to Excel format (and saved as such). When the book autoupdates on opening, it is able to update all sheets except for those updating from the CSV-converted-XLS sources--unless the actual sources are opened at that time, I get a table full of #VALUE! errors... and when I open the related files, it goes back to the proper values. A key problem lies in the fact that the two sources which return the bad values are for just one week of data. Each week, there's a new source for both. So by end of year, I'd be looking at nearly 100 books I'd have to open in order to properly update... obviously not an option. Has anyone ever experienced this? I've linked to dozens of books in the past and never incurred this problem; I suspect it's because these are converted from ..CSV files but I have no idea how to go about fixing it. Any ideas? Appreciate any help anyone can provide. Thanks Jamie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE! error in master when linked sheets aren't open?
Note--it appears the problem actually is originating with the SUMIF I'm using while referring to the cell... here's the actual value of the master cell: =SUMIF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!C:C, "Entered", 'C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!D:D) Is SumIf only able to perform a function on a given range if the related sheet is open? "MJW" wrote: Hi All, I've run into a bit of a weird problem in a master workbook I'm creating. In essence, it's a roll-up of about 20 different multi-departmental metrics, and links to nearly as many sources. Out of all of these sources, two of them are CSV files converted to Excel format (and saved as such). When the book autoupdates on opening, it is able to update all sheets except for those updating from the CSV-converted-XLS sources--unless the actual sources are opened at that time, I get a table full of #VALUE! errors... and when I open the related files, it goes back to the proper values. A key problem lies in the fact that the two sources which return the bad values are for just one week of data. Each week, there's a new source for both. So by end of year, I'd be looking at nearly 100 books I'd have to open in order to properly update... obviously not an option. Has anyone ever experienced this? I've linked to dozens of books in the past and never incurred this problem; I suspect it's because these are converted from .CSV files but I have no idea how to go about fixing it. Any ideas? Appreciate any help anyone can provide. Thanks Jamie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE! error in master when linked sheets aren't open?
There are some worksheet functions that won't work with closed workbooks--but
IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() You could use an array formula: =SUM(IF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!C1:C99, "Entered", 'C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!D1:D99)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. You could use =sumproduct(), too. This doesn't need to be array entered, but it suffers from the same range limitations as the array formula: =SUMproduct(--('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!C1:C99="Entered"), 'C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!D1:D99) === In either case, I find it much easier creating the formula when the "sending" workbook is open. Then when I close that workbook, excel will adjust the formula to include the path and filename. MJW wrote: Note--it appears the problem actually is originating with the SUMIF I'm using while referring to the cell... here's the actual value of the master cell: =SUMIF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!C:C, "Entered", 'C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly Data\[RMA_Aging072807.xls]RMA_Aging072807'!D:D) Is SumIf only able to perform a function on a given range if the related sheet is open? "MJW" wrote: Hi All, I've run into a bit of a weird problem in a master workbook I'm creating. In essence, it's a roll-up of about 20 different multi-departmental metrics, and links to nearly as many sources. Out of all of these sources, two of them are CSV files converted to Excel format (and saved as such). When the book autoupdates on opening, it is able to update all sheets except for those updating from the CSV-converted-XLS sources--unless the actual sources are opened at that time, I get a table full of #VALUE! errors... and when I open the related files, it goes back to the proper values. A key problem lies in the fact that the two sources which return the bad values are for just one week of data. Each week, there's a new source for both. So by end of year, I'd be looking at nearly 100 books I'd have to open in order to properly update... obviously not an option. Has anyone ever experienced this? I've linked to dozens of books in the past and never incurred this problem; I suspect it's because these are converted from .CSV files but I have no idea how to go about fixing it. Any ideas? Appreciate any help anyone can provide. Thanks Jamie -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a copy with all data linked to the master | Excel Discussion (Misc queries) | |||
Updating tabs linked to master tab | Excel Discussion (Misc queries) | |||
Open and copy all workbook sheets in a folder to a master file | Excel Discussion (Misc queries) | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions |