Func to check if a worksheet exists

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
 
F

Frank Kabel

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
 
J

Joe Blow

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
 
F

Frank Kabel

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...
 
J

Joe Blow

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
 
F

Frank Kabel

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...
 
J

Joe Blow

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
 
F

Frank Kabel

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...
 
J

Joe Blow

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
 
F

Frank Kabel

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...
 
J

Joe Blow

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.
 
F

Frank Kabel

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...
 
H

Harlan Grove

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.
 
F

Frank Kabel

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...
 
J

Joe Blow

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conflicting vlookups 1
Prompt For Replace in Selection And More... 0
VBA for Dependents shortcut menu 0
How to check to see if a sheet with a particular name exists? 3
Need help finding a substring in a array of strings. 1
Multiple VLOOKUP in an IF statement 2
VBA word change keys 0
If statement checking list 4

Top