Update data from closed workbook with sumif formula

[leopaulc]

Hi Dear all,

I was trying to link two workbooks for manpower utility...
The workbooks are as below.

1.) Daily Workbook include the details of work (in column G) and Time Taken (in column F). This workbook is filled by the individual.
2.) A Master Workbook include the team member list (in C4 to AD4) , Month list (in B6 to B17).

The attached files are for the ref. XYZ file is filled by individuals and output file is the Master workbook

I was trying to update the link bychanging the work (Parameter to calculate the hrs used) from B3. Please suggest me what kind of formula / VB code to be applied in to the Master Workbook????


Please Help....


Thanks in advance..

Leo

[leopaulc]

Hi.....

Thanks for the response.....

The workbook will not be open all the time.. The XYZ Kind of files will be approx 25. The all files are not possible to open to update the output file.. is there any other option to link the inactive file? (Similar to Update links)??????


Thanks in advance

[Admin]

Hi Leo,

In my opinion, you must run a consolidation macro to update your Master workbook. You could find some useful links here
If you need further help on this or macro, post back.. happy to help.

[Excel Fox]

You could use the entire path of the file within the formula

Like for example

=SUMIF('G:\ExcelFox\[Name of the workbook.xlsx]NameOfSheet'!C6:C14,"FOX",'G:\ExcelFox\[Name of the workbook.xlsx]NameOfSheet'!B6:B14)

[Admin]

Hi,

I accidently delete my earlier post.

It can be done using INDIRECT function. However, to work with INDIRECT the other workbook must be open.

PFA.