I need to have this done by tomorrow. I have two files, basically each
employee has there own "log", it logs hours spent on a project A, B, C, etc.
Then I have one master file that has all the project codes (ie. A, B,C,etc)
and descriptions. I want the master file to link to each individual file and
do a sumIF formula that will sum if it meets the criteria. My current system
displays an error of #VALUE when the linked document is closed. When its
open it then displays the real values. I don't know what is wrong, please
help?

the linked file needs to be opened

run the maco recorder and open the file
then insert this code in the workbook open event,
so whenever the main workbook opens, it will open the other workbok as
well,
search your forum and search for open workbooks when opening an excel
file,
or opening workbooks in open workbook event, its done all the time.
there are ways to link files to a closed workbook, but that takes a
while to explain, If you really want to do that then search your forum,
for linking closed workbooks


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519573

"davesexcel" wrote:


the linked file needs to be opened

run the maco recorder and open the file
then insert this code in the workbook open event,
so whenever the main workbook opens, it will open the other workbok as
well,
search your forum and search for open workbooks when opening an excel
file,
or opening workbooks in open workbook event, its done all the time.
there are ways to link files to a closed workbook, but that takes a
while to explain, If you really want to do that then search your forum,
for linking closed workbooks


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519573


Dave,
Seriously, the main workbook needs to open all the other workbooks - this is
like 25 workbooks that all feed into the summaries. What value is linking
them if you need to open them all up to get data to transfer. Do you know if
this is just because I am using the sumif? because if I link, general
formulas and/or text it works as expected?

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))

Adjust the range to match--but you can't use the whole column.

If you create the formula with the book1.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

Rookie_User wrote:

I need to have this done by tomorrow. I have two files, basically each
employee has there own "log", it logs hours spent on a project A, B, C, etc.
Then I have one master file that has all the project codes (ie. A, B,C,etc)
and descriptions. I want the master file to link to each individual file and
do a sumIF formula that will sum if it meets the criteria. My current system
displays an error of #VALUE when the linked document is closed. When its
open it then displays the real values. I don't know what is wrong, please
help?

--

Dave Peterson