Workbook Reference Advice

[EvonS]

Hello Everyone,


I would like to compare data from multiple workbooks but I am finding it quite cumbersome to reference all the sheets in formulas. I'm also finding it cumbersome because I can't seem to make the workbook references relative either (i.e. C:\mydir\workbooks\file.xls vs \file.xls) and the file references keep breaking. is there an easier way to do this other than putting all the data in one sheet? Here's an example of a formula:

=(COUNTIF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - DE 2018.xlsm]Data Fields'!$N$2:$N$9000, A2)+COUNTIF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - DW 2018.xlsm]Data Fields'!$N$2:$N$9000, A2)+COUNTIF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - REX 2018.xlsm]Data Fields'!$N$2:$N$9000, A2)+COUNTIF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - NS 2018.xlsm]Data Fields'!$N$2:$N$9000, A2))

Thanks in advance for your help.

 

[mrshl9898]

Hi,

COUNTIF won't work on closed workbooks, I assume this is why it's breaking.

https://www.mrexcel.com/forum/excel-questions/46473-using-countif-closed-workbooks.html

Try this array (use ctrl+shift+enter to confirm it)

=COUNT(IF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - DE 2018.xlsm]Data Fields'!$N$2:$N$9000=A2,1))+COUNT(IF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - DW 2018.xlsm]Data Fields'!$N$2:$N$9000=A2,1))+COUNT(IF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - REX 2018.xlsm]Data Fields'!$N$2:$N$9000=A2,1))+COUNT(IF('Z:\Analysis and Reporting\FOCUS 2017-2018 YTD Analysis\Discussion Notes\2018\[Discussion Notes - NS 2018.xlsm]Data Fields'!$N$2:$N$9000=A2,1))

 

[EvonS]

Thank you. That was very helpful.