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

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
152
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=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)
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top