Func to check if a worksheet exists

  • Thread starter Thread starter Joe Blow
  • Start date Start date
J

Joe Blow

Hi,

I need a function that returns true or false if a worksheet exits in
an unopened separate workbook. Any ideas?

Thanks,
Joe
 
Hi
try something like
sub foo()
dim wks as worksheet
on error resume next
set wks = worksheets("sheetx")
on error goto 0
if wks is nothing then
msgbox "sheetx does not exist
end if
end sub
 
Hi
try something like
sub foo()
dim wks as worksheet
on error resume next
set wks = worksheets("sheetx")
on error goto 0
if wks is nothing then
msgbox "sheetx does not exist
end if
end sub
Click to expand...

Hi Frank,

Here is the context of how I would like it to work:
IF(ISERR('[other sheet.xls]6.04'!$A$11),VLOOKUP('[other
sheet.xls]6.04'!$A$11,'[other sheet.xls]6.04'!$A$7:$G$19,7,0),"-")

if my iserr or some other func that check if this ref is valid returns
false, I get a -, if the ref is valid, I get the lookup.

The iserr is not working this way, it says I have entered my formula
incorrectly because the ref is invalid.

Any ideas?

Joe
 
Hi
in this case I'd use the following (will cover all kind of
errors):
IF(ISERROR(VLOOKUP('[othersheet.xls]6.04'!$A$11,'[other
sheet.xls]6.04'!$A$7:$G$19,7,0)),"-",VLOOKUP
('[othersheet.xls]6.04'!$A$11,'[other sheet.xls]6.04'!
$A$7:$G$19,7,0))
-----Original Message-----
Hi
try something like
sub foo()
dim wks as worksheet
on error resume next
set wks = worksheets("sheetx")
on error goto 0
if wks is nothing then
msgbox "sheetx does not exist
end if
end sub
Click to expand...

Hi Frank,

Here is the context of how I would like it to work:
IF(ISERR('[other sheet.xls]6.04'!$A$11),VLOOKUP('[other
sheet.xls]6.04'!$A$11,'[other sheet.xls]6.04'! $A$7:$G$19,7,0),"-")

if my iserr or some other func that check if this ref is valid returns
false, I get a -, if the ref is valid, I get the lookup.

The iserr is not working this way, it says I have entered my formula
incorrectly because the ref is invalid.

Any ideas?

Joe
.
Click to expand...
 
Hi
in this case I'd use the following (will cover all kind of
errors):
IF(ISERROR(VLOOKUP('[othersheet.xls]6.04'!$A$11,'[other
sheet.xls]6.04'!$A$7:$G$19,7,0)),"-",VLOOKUP
('[othersheet.xls]6.04'!$A$11,'[other sheet.xls]6.04'!
$A$7:$G$19,7,0))
Click to expand...

Hi Frank,

That also returns the #REF as the sheet 6.04 do not exist yet. Any
other options?

Thanks,
Joe
 
Hi
not tested but try
IF(ISERROR(VLOOKUP(INDIRECT("'[othersheet.xls]6.04'!
$A$11"),INDIRECT("'[other
sheet.xls]6.04'!$A$7:$G$19"),7,0)),"-",VLOOKUP
(INDIRECT("'[othersheet.xls]6.04'!$A$11"),INDIRECT
("'[other sheet.xls]6.04'!
$A$7:$G$19"),7,0))



-----Original Message-----
Hi
in this case I'd use the following (will cover all kind of
errors):
IF(ISERROR(VLOOKUP('[othersheet.xls]6.04'!$A$11,'[other
sheet.xls]6.04'!$A$7:$G$19,7,0)),"-",VLOOKUP
('[othersheet.xls]6.04'!$A$11,'[other sheet.xls]6.04'!
$A$7:$G$19,7,0))
Click to expand...

Hi Frank,

That also returns the #REF as the sheet 6.04 do not exist yet. Any
other options?

Thanks,
Joe
.
Click to expand...
 
Hi
not tested but try
IF(ISERROR(VLOOKUP(INDIRECT("'[othersheet.xls]6.04'!
$A$11"),INDIRECT("'[other
sheet.xls]6.04'!$A$7:$G$19"),7,0)),"-",VLOOKUP
(INDIRECT("'[othersheet.xls]6.04'!$A$11"),INDIRECT
("'[other sheet.xls]6.04'!
$A$7:$G$19"),7,0))
Click to expand...
Hi Frank,

That also returns #REF.

Regards,
Joe
 
Hi Joe
I just tried it on my system and it works without any problem. It
returns '-' as I don't have this other file.

--
Regards
Frank Kabel
Frankfurt, Germany


Joe said:
Hi
not tested but try
IF(ISERROR(VLOOKUP(INDIRECT("'[othersheet.xls]6.04'!
$A$11"),INDIRECT("'[other
sheet.xls]6.04'!$A$7:$G$19"),7,0)),"-",VLOOKUP
(INDIRECT("'[othersheet.xls]6.04'!$A$11"),INDIRECT
("'[other sheet.xls]6.04'!
$A$7:$G$19"),7,0))
Click to expand...
Hi Frank,

That also returns #REF.

Regards,
Joe
Click to expand...
 
Hi Joe
I just tried it on my system and it works without any problem. It
returns '-' as I don't have this other file.
Click to expand...

Hi Frank,

Thanks for checking but it's not working in my application. Here is an
example that would work for me, but it doesn't.
ISERROR('[other workbook.xls]Sheet1'!$A$1). This returns false when
Sheet1 exists, when it doesn't, excel won't let you enter it. What I
need is a function that returns true or false based on if a sheet
exists in a spreadsheet. I guess it's harder than it sounds.

Thanks again,
Joe
 
Hi Joe
I posted a solution for this (using INDIRECT). Try in your example:
=ISERROR(INDIRECT("'[other workbook.xls]Sheet1'!$A$1"))

--
Regards
Frank Kabel
Frankfurt, Germany

Joe Blow said:
Hi Joe
I just tried it on my system and it works without any problem. It
returns '-' as I don't have this other file.
Click to expand...

Hi Frank,

Thanks for checking but it's not working in my application. Here is an
example that would work for me, but it doesn't.
ISERROR('[other workbook.xls]Sheet1'!$A$1). This returns false when
Sheet1 exists, when it doesn't, excel won't let you enter it. What I
need is a function that returns true or false based on if a sheet
exists in a spreadsheet. I guess it's harder than it sounds.

Thanks again,
Joe
Click to expand...
 
Hi Joe
I posted a solution for this (using INDIRECT). Try in your example:
=ISERROR(INDIRECT("'[other workbook.xls]Sheet1'!$A$1"))
Click to expand...

Hi Frank,
ISERROR(INDIRECT('[other workbook.xls]Sheet1'!$A$1)) TRUE where
Sheet1 exists
ISERROR(INDIRECT("'[other workbook.xls]Sheet2'!$A$1"))TRUE where
Sheet2 does not.

Both return true.
 
Hi Jow
the other workbook has to be open to make this work. Otherwise INDIRECT
will always return an error

--
Regards
Frank Kabel
Frankfurt, Germany

Joe Blow said:
Hi Joe
I posted a solution for this (using INDIRECT). Try in your example:
=ISERROR(INDIRECT("'[other workbook.xls]Sheet1'!$A$1"))
Click to expand...

Hi Frank,
ISERROR(INDIRECT('[other workbook.xls]Sheet1'!$A$1)) TRUE where
Sheet1 exists
ISERROR(INDIRECT("'[other workbook.xls]Sheet2'!$A$1"))TRUE where
Sheet2 does not.

Both return true.
Click to expand...
 
the other workbook has to be open to make this work. Otherwise INDIRECT
will always return an error
Click to expand...
...

But the OP stated in the beginning, "I need a function that returns true or
false if a worksheet exits in an unopened separate workbook." Why are you now
assuming that the workbook must be open?

For the OP: you'll need to use the pull funtion mentioned in alternative 4 in

http://www.google.com/[email protected]

For example,

=IF(ISERROR(1/(ERROR.TYPE(pull("'x:\y\[z.xls]SheetToCheck'!IV65536"))=4)),
VLOOKUP(x,pull("'x:\y\[z.xls]SheetToCheck'!A1:D100"),4,0),"")

This won't work when the referenced IV65536 cell itself evaluates to #REF!, but
that's usually not the case.
 
Hi Harlan
wrong assumption on my side based on the OP's example formula (not
using any path information)

--
Regards
Frank Kabel
Frankfurt, Germany

Harlan Grove said:
the other workbook has to be open to make this work. Otherwise INDIRECT
will always return an error
Click to expand...
..

But the OP stated in the beginning, "I need a function that returns true or
false if a worksheet exits in an unopened separate workbook." Why are you now
assuming that the workbook must be open?

For the OP: you'll need to use the pull funtion mentioned in alternative 4 in

http://www.google.com/[email protected]
er.com

For example,

=IF(ISERROR(1/(ERROR.TYPE(pull("'x:\y\[z.xls]SheetToCheck'!IV65536"))=4
)),
VLOOKUP(x,pull("'x:\y\[z.xls]SheetToCheck'!A1:D100"),4,0),"")

This won't work when the referenced IV65536 cell itself evaluates to #REF!, but
that's usually not the case.
Click to expand...
 
Hi Harlan
wrong assumption on my side based on the OP's example formula (not
using any path information)
Click to expand...

Hi Frank, I had them in the same directory for simplicity. I
downloaded Harlan pull.bas. How do I use it? Where do I put it?

Regards,
Joe
 
the other workbook has to be open to make this work. Otherwise INDIRECT
will always return an error
Click to expand...
..

But the OP stated in the beginning, "I need a function that returns true or
false if a worksheet exits in an unopened separate workbook." Why are you now
assuming that the workbook must be open?

For the OP: you'll need to use the pull funtion mentioned in alternative 4 in

http://www.google.com/[email protected]

For example,

=IF(ISERROR(1/(ERROR.TYPE(pull("'x:\y\[z.xls]SheetToCheck'!IV65536"))=4)),
VLOOKUP(x,pull("'x:\y\[z.xls]SheetToCheck'!A1:D100"),4,0),"")

This won't work when the referenced IV65536 cell itself evaluates to #REF!, but
that's usually not the case.
Click to expand...

Hi Harlen,

I don't know how to utilize your pull function. I have your pull.bas
but I need direction on using. Could you give me a hand or a pointer?

Also, I only see Alt 3 in the link above

Thanks.

Regards,
Joe
 
Back
Top