Variable Sheet reference without INDIRECT

Richierog

New Member
Joined
Nov 7, 2017
Messages
2
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top