Formula (INDIRECT & Array) suddenly stopped working

Dear friends,

Just now my indirect formula stopped working, whilst I am 99% confident nothing changed.

To provide some context; I am pulling specific data points from a number of worksheets. To make this process easier I am using a combination of INDIRECT and array.

Exact example:

=INDIRECT("'["&i.Name.file.data.Workbook&"]"&x.Name.sheet.list.Worksheet&"'!Revenues")

My workbook array name is a single cell, since it is just one workbook (i.Name.file.data.Workbook)
My worksheet array name is a list / column with different values (Worksheet 1, Worksheet 2, Worksheet 3 etc.) (x.Name.sheet.list.Worksheet)

Suddenly this no longer works - the formula that is in there still does want it needs to do, e.g. if I change the inputs in one of the worksheets the outputs do change. But when I re-calculate it stops.

Interestingly, when I replace the array name for worksheets with a hardcoded value e.g."Worksheet 1" everything works again.

So the error seems to be in the worksheet array, as if Excel is not happy with a column here as opposed to a single value. But it has worked before, so this should be possible

Very curious to your feedback and many thanks in advance

Cheers,
Frank

INDIRECT can not refer to a closing workbook.
May be try to open the sourcing workbook?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I shall provide the link for you today: https://www.mrexcel.com/board/thread...rking.1173700/.)