Use cell value as reference to closed workbook?

Hey guys,

I have a workbook with a list of values.
This file name changes every week (Due to the date)
This workbook is closed.

In a separate workbook i have a cell which concatenates multiple cells to make up the path and file name of the above closed workbook.
I would like to use the value of this cell, as a reference.
I have two ways in which i would like this to work.

Preferably;

I would like to use the value as a reference within a "=COUNTIF(" to count the amount of times a certain value appears in the list in the closed workbook.

OR!

[Add a countif to the closed workbook]

Then use the value in the cell in the open workbook as a reference to the result of the countif in the closed workbook.

Does this make any sense?

If so any help would be much appreciated!

Thanks in advance!

Chris

Hi and welcome to the forum

The only (formula) way to use a reference like that (the concatenate 1) is with indirect(). However, indirect() by itself does not work on closed workbooks You will need to also get the morefunc add-in