Complex question: Indirect & Array combination not working

[FrankieG]

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 (or array in the INDIRECT formula) here as opposed to a single hardcoded value. But it has worked before, so this should be possible

Very curious to your feedback and many thanks in advance

Cheers,
Frank

 

[earthworm]

I think you are missing an exclamation (!" in front of sheet name

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula (INDIRECT & Array) suddenly stopped working
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.