Indirect Alternatives

[Graham H]

I have large numbers of cells which contain a formula as below

=IF(ISERROR(INDIRECT("'" & A2 & "'!C41")),0,INDIRECT("'" & A2 & "'!C41"))

This does exactly what it is supposed to but it definately creates a slower calculation
process when set on automatic calculation which really needs to be on. I checked using
Fastexcel and the block is creating a bottleneck. Is there another alternative to this
which would produce the same result but faster? I would welcome any guidance.

Graham

 

[Harlan Grove]

=IF(ISERROR(INDIRECT("'" & A2 & "'!C41")),0,INDIRECT("'" & A2 & "'!C41"))

Are you using an ISERROR check to avoid #REF! errors when cell A2
doesn't contain the name of a worksheet in an open workbook or to
catch errors in the cells in the other worksheets?

. . . Is there another alternative to this
which would produce the same result but faster? I would welcome any guidance.

Maybe. If you're working with just a few other worksheets (say A, B, C
and D), you could try something like this. Define the following names.

Aws =A!$1:$65536
Bws =B!$1:$65536
Cws =C!$1:$65536
Dws =D!$1:$65536
_WS =CHOOSE(MATCH($A$2,{"A";"B";"C";"D"},0),Aws,Bws,Cws,Dws)

Then replace your formula above with

=IF(ISERROR(INDEX(_WS,41,3)),0,INDEX(_WS,41,3))

or without the hardcoding

=IF(ISERROR(INDEX(_WS,ROW(C41),COLUMN(C41))),0,INDEX(_WS,ROW
(C41),COLUMN(C41)))

This will eliminate volatile function calls, which should speed things
up.

 

[Graham H]

Thanks Harlan,
The ISERROR is being used to avoid the #REF! error when cell A2 doesn't
contain the name of the worksheet.

The problem is with the worksheets in that both the number and name of the worksheets will
be unknown. What I am trying to say is that as part of the workbook someone lists down a
range of fields (land) which they have with whatever name they choose to call them. The
number of fields and subsequently worksheets can be variable but the workbook then
creates a worksheet for every field with the name of the worksheet being the name of the
field. The formula then links to A2 which is the name of the first worksheet. Hope this is
making some sense.

Graham

 

[Pete_UK]

The ISERROR is being used to avoid the #REF! error when cell A2 doesn't

contain the name of the worksheet

In that case you could just use:

=IF(A2="", 0,INDIRECT("'" & A2 & "'!C41"))

and this should speed things up considerably.

Hope this helps.

Pete

 

[Graham H]

Many thanks to all for their help. It is most appreciated.

Graham