Variable Sheet reference without INDIRECT

[Richierog]

I have a sheet in a workbook that carries out multiple checks on each of the other sheets in the workbook, looking at the same cells on each. So far the developer has manually written each check, such as IF('Sheet1'!A1=TRUE,1,0). However, there are 100+ sheets with 5 checks for each.

I have create an array SheetNames so that I can list all sheets using INDEX(SheetNames, B:B) where column B has ascending references 1 to 100. I have can create a inelegant (ie quick) method of using this o create a sheet/cell reference by CONCATENATING the various elements ie the ' SheetNames ' ! A1, I am then able to use a single formula for all of the checks with INDIRECT , using the concatenated reference, making the formulae dynamic and simple to create, change and audit.

This works, but adds 500+ formulae with INDIRECT - the workbook itself is complex but has been created with efficiency and simplicity in mind, so I don't want to change this with multiple INDIRECTs.

Any assistance to avoid INDIRECT would be welcome

 

[Yongle]

This is a weak area in Excel and INDIRECT is the only formula solution (unless you create all the individual formulas!)

I suggest you consider using VBA