Connection between 2 workbooks

chemhany

New Member
Joined
Mar 3, 2021
Messages
22
Office Version
  1. 2019
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,285
To change the worksheet name you have to use Indirect:
VBA Code:
=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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,254
Messages
5,769,055
Members
425,513
Latest member
9375021758

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
Top