Increment Sheet Count when dragging formula

Hi!!

I have a formula i.e. =SUMIF(INDIRECT("'[Stock Register.xlsx]Stock Code " & ROW(A1) & "'!" & "$O$8:$O$500"), "Issued to Sanitary Syndicate", INDIRECT("'[Stock Register.xlsx]Stock Code " & ROW(A1) & "'!" & "$I$8:$I$500")

Above formula basically sums up the total with if condition, and it is displaying results perfectly but the problem is when the external source is closed, the result will automatically changed to #Ref error.

Please advice how to deal with it.

Thanks in advance.

Basically, you'll need to incorporate your formula into a VBA macro. A formula by itself will not be able to open a closed workbook. By incorporating the formula into a VBA macro
the coding in the macro can open the closed workbook, run the formula and capture the information you seek, then close the other workbook.


Here is what M$ has to say :

https://support.office.com/en-us/article/Create-an-external-reference-link-to-a-cell-range-in-another-workbook-78344b0c-3458-474d-833e-6d0d55caf96a


Here is an example using VBA and and embedded formula:

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm