Alternative to Indirect Function to reference various worksheets

[dviper]

I'm dealing some very large files that make liberal use of the Indirect function. The calculation is very, very slow. Indirect is used to select a particular worksheet (out of the 75 worksheets in the workbook). Can I use Index and Match to do the same?

 

[dviper]

Anyone have any ideas?

 

[Domenic]

See if this will work for you. Let's assume the following...

A2 contains the sheet name, let's say Sheet1

B2 contains the formula =COUNTIF(INDIRECT("'"&A2&"'!A:A"),"x")

We can replace INDIRECT with a combination of CHOOSE and MATCH...

=COUNTIF(CHOOSE(MATCH(A2,{"Sheet2","Sheet3","Sheet4"},0),'Sheet2'!A:A,'Sheet3'!A:A,'Sheet4'!A:A),"x")

Additional sheets can be added, as necessary.

Hope this helps!

 

[Jerry Sullivan]

From reading the OP, I'm not sure whether the Sheet Name being used by the Indirect formulas could be any one of the 75 sheets, or just a small subset. Domenic's suggestion to use a Choose function formula to directly return a range is a good one; however the formulas would become quite lengthy if they need to reference all 75 sheets.

One option would be to move the expression that returns the range from the selected sheet to a Dynamic Named Range. This thread has an example based on the choice of 7 sheets...
http://www.mrexcel.com/forum/excel-questions/827663-excel-worksheet-reference-using-ddd-format.html

If you are open to using some VBA, you could have a Named Range referenced by your worksheet formulas that is updated by VBA to reflect the currently selected sheet.

So your formula might look like:
=COUNTIF(MyRange, "x")

When "Sheet2" is selected from a dropdown in A2, the Named Range "MyRange" would be updated by VBA to
Refers to: "=Sheet2!$A:$A"

When "Sheet75" is selected from a dropdown in A2, the Named Range "MyRange" would be updated by VBA to
Refers to: "=Sheet75!$A:$A"

This VBA approach would be a bit more adaptable if you add or remove Worksheets.