check if worksheet exists
 

How can I check if a worksheet exists before trying the access it? I've been
using the if text on an iserror for an indirect function, but this causes the
calculation to be very slow when entered into many cells.

It sounds like the sheet is slow because each cell with the formula is
checking the hard disk for the file. Try putting the sheet check into just
one cell giving say 0 for non-existing & 1 for existing, then use an if
condition in the other cells to check that cell?


--
Ian
--
"joeeng" wrote in message
...
How can I check if a worksheet exists before trying the access it? I've
been
using the if text on an iserror for an indirect function, but this causes
the
calculation to be very slow when entered into many cells.

I have a situation where there may be up to 250 sheets, say A1, A2,...,A250,
but usually there will be many less than 250, say A1, A2,...,A50, but I have
to check the whole 250 because the number of existing sheets will not
automatically be known. In this case I do have to check all 250 possible
sheets to see just how many exist. Accessing each one and checking if it is
an error takes a very long time to calculate the initial cell values upon
opening the file and upon recalculation of the cell values. There should be
a way to check if a sheet exists without actually trying to access it with an
INDIRECT function. Thanks.

"Ian" wrote:

It sounds like the sheet is slow because each cell with the formula is
checking the hard disk for the file. Try putting the sheet check into just
one cell giving say 0 for non-existing & 1 for existing, then use an if
condition in the other cells to check that cell?


--
Ian
--
"joeeng" wrote in message
...
How can I check if a worksheet exists before trying the access it? I've
been
using the if text on an iserror for an indirect function, but this causes
the
calculation to be very slow when entered into many cells.

I've never used it, but I see there is a FileExists method in Excel VBA.
Perhaps you could have a look at that. As I say, I've never used it, so I
can't advise you further. Sorry

--
Ian
--
"joeeng" wrote in message
...
I have a situation where there may be up to 250 sheets, say A1,
A2,...,A250,
but usually there will be many less than 250, say A1, A2,...,A50, but I
have
to check the whole 250 because the number of existing sheets will not
automatically be known. In this case I do have to check all 250 possible
sheets to see just how many exist. Accessing each one and checking if it
is
an error takes a very long time to calculate the initial cell values upon
opening the file and upon recalculation of the cell values. There should
be
a way to check if a sheet exists without actually trying to access it with
an
INDIRECT function. Thanks.

"Ian" wrote:

It sounds like the sheet is slow because each cell with the formula is
checking the hard disk for the file. Try putting the sheet check into
just
one cell giving say 0 for non-existing & 1 for existing, then use an if
condition in the other cells to check that cell?


--
Ian
--
"joeeng" wrote in message
...
How can I check if a worksheet exists before trying the access it?
I've
been
using the if text on an iserror for an indirect function, but this
causes
the
calculation to be very slow when entered into many cells.