Connection between 2 workbooks

[chemhany]

I have 2 workbooks and I want to connect both to calculate some data. In the 1st one, I have several rows in different worksheets (31 sheets). I want to use COUNTIF function in the 2nd one. This is the function
=COUNTIF('C:\Users\lab9\Desktop\H\shift\[Shift Report 3-2021.xlsx]1'!$I$62:$I$64,B1)
The question is how to increment the rows in the 2nd workbook,i.e, how to change the number "1" in the function when dragging down

Thanks

 

[Anthony47]

To change the worksheet name you have to use Indirect:

=COUNTIF(INDIRECT("'C:\Users\lab9\Desktop\H\shift\[Shift Report 3-2021.xlsx]"&ROW(A1)&"'!$I$62:$I$64"),B1)

Of course this works if the name of the sheets is 1, 2, 3, ...
The drawback of using Indirect is that the second workbook must be open for the formula to work

Bye

 

[chemhany]

Thanks so much

That is work perfectly