The master file which consolidates values from other files shows #VALUE when the files are closed

[Zakky]

Hi,
I have created a master consolidation file, which is pulling values from other files by using SUMIF function. The problem i have is that when i open the consolidation file it always shows #VALUE! in all cells that have the SUMIF formula. To get rid of the errors i have to open all the other files every time i want to see the values. Is there a way to stop this from happening as it's time consuming open all files to review the consolidated file? Any help would be much appreciated. Thank you in advance.

 

[footoo]

Post the formula.

 

[Zakky]

=SUMIF('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Face to Face.xlsx]Summary'!$A:$A,$A61,'G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Face to Face.xlsx]Summary'!N:N)+SUMIF('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Distance.xlsx]Summary'!$A:$A,$A61,'G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Distance.xlsx]Summary'!N:N)

 

[footoo]

See if this helps:
https://www.mrexcel.com/forum/excel...f-closed-workbooks-returning-value-error.html

 

[Zakky]

Oooh!...i'll give it a go. Thank you!

 

[Zakky]

It works! Thank you. It's a strange function. when i start the lookup ref from, say, A1:A50 it gives #VALUE! but when i change it to A2:A50 it works. It shouldn't matter which rows i start the lookup reference?

 

[Zakky]

It's not working as expected. It keeps the values when the files are closed but the values don't agree/make sense. I just want to sum all of the values in each file into one file by using something that is same as SUMIF but works with closed files. The SUMPRODUCT gives results for some of the cells that just don't add up. Maybe it's me getting the function wrong. i want to consolidate values from 2 files so i've prepared the below. I'm telling it to look in A1:A100 range for "Sales" and sum up the value in N1:N100 range. Am i doing anything wrong? For some cells it returns a correct value but for some it returns silly numbers. Have i done it right for two separate files - should i be using two separate SUMPRODUCT functions? i need to build a build with 5 files into 1 but i can't move forward because it won't work with closed files. In column 'A' I have stored the lookup references and values in 'N' (Jan) with Feb in O and so on. Any help is appreciated. Thank you!

=SUMPRODUCT(--('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Face to Face.xlsx]Summary'!$A$1:$A$100="Sales")*('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Face to Face.xlsx]Summary'!N$1:N$100))+SUMPRODUCT(--('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Distance.xlsx]Summary'!$A$1:$A$100="Sales")*('G:\Finance\1 - Dept Only\Mmgt\Management Reporting\FY17\10-June-17\Forecast\Nursing\[FY17 Nursing Reveue Model - nDIP June17 Distance.xlsx]Summary'!N$1:N$100))