Formula Error
 

In the formula below I'm checking to see if a file exits. If it doesn't
then zero is suppose to be the value in the cell. If the file does exists
then it's suppose to supply the value from a cell in one of the worksheets of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT Daily
Status 061208.xls]Executive Summary'!$C$30)

Formula Error
 

Hi El Bee,

The problem you're having is that your formula is testing the quoted string,
not for the presence of a file.

To test for a file you need some vba. Here's a suitable function:

Application.Volatile

Function WkBkExists(WkBk As String)
If InStr(1, WkBk, "[") Then
WkBk = Left(WkBk, InStr(1, WkBk, "[") - 1)
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function

To use this function:
.. open your Excel Workbook
.. press Alt-F11 to open the vba editor
.. insert a code module
.. copy the code & paste it into your module
.. press Alt-F11 again to return to the workbook
.. code your formula as:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\[IT Daily Status 061208.xls]Executive
Summary'!$C$30),"\\PANTHER\depts\TechnicalServices \Daily Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)

Cheers

--
macropod
[MVP - Microsoft Word]


"El Bee" wrote in message
...

In the formula below I'm checking to see if a file exits. If it doesn't
then zero is suppose to be the value in the cell. If the file does exists
then it's suppose to supply the value from a cell in one of the worksheets
of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT Daily
Status 061208.xls]Executive Summary'!$C$30)

Formula Error
 

Actually, the formula could be reduced to:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\IT Daily Status
061208.xls),"\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)
All you really need is the path & filename.

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi El Bee,

The problem you're having is that your formula is testing the quoted
string,
not for the presence of a file.

To test for a file you need some vba. Here's a suitable function:

Application.Volatile

Function WkBkExists(WkBk As String)
If InStr(1, WkBk, "[") Then
WkBk = Left(WkBk, InStr(1, WkBk, "[") - 1)
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function

To use this function:
. open your Excel Workbook
. press Alt-F11 to open the vba editor
. insert a code module
. copy the code & paste it into your module
. press Alt-F11 again to return to the workbook
. code your formula as:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\[IT Daily Status 061208.xls]Executive
Summary'!$C$30),"\\PANTHER\depts\TechnicalServices \Daily Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)

Cheers

--
macropod
[MVP - Microsoft Word]


"El Bee" wrote in message
...

In the formula below I'm checking to see if a file exits. If it doesn't
then zero is suppose to be the value in the cell. If the file does
exists
then it's suppose to supply the value from a cell in one of the
worksheets
of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT Daily
Status 061208.xls]Executive Summary'!$C$30)

Formula Error
 

A correction:
The line
Application.Volatile
should apear after the line
Function WkBkExists(WkBk As String)

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi El Bee,

The problem you're having is that your formula is testing the quoted
string,
not for the presence of a file.

To test for a file you need some vba. Here's a suitable function:

Application.Volatile

Function WkBkExists(WkBk As String)
If InStr(1, WkBk, "[") Then
WkBk = Left(WkBk, InStr(1, WkBk, "[") - 1)
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function

To use this function:
. open your Excel Workbook
. press Alt-F11 to open the vba editor
. insert a code module
. copy the code & paste it into your module
. press Alt-F11 again to return to the workbook
. code your formula as:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\[IT Daily Status 061208.xls]Executive
Summary'!$C$30),"\\PANTHER\depts\TechnicalServices \Daily Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)

Cheers

--
macropod
[MVP - Microsoft Word]


"El Bee" wrote in message
...

In the formula below I'm checking to see if a file exits. If it doesn't
then zero is suppose to be the value in the cell. If the file does
exists
then it's suppose to supply the value from a cell in one of the
worksheets
of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT Daily
Status 061208.xls]Executive Summary'!$C$30)

Formula Error
 

And another correction. Sorry 'bout this. Here's the complete function:

Function WkBkExists(WkBk As String)
Application.Volatile
If InStr(1, WkBk, "[") < 0 Then
WkBk = Replace(Left(WkBk, InStr(1, WkBk, "]") - 1), "[", "")
End If
If Dir(WkBk, vbNormal) = "" Then
FileExists = False
Else
FileExists = True
End If
End Function

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi El Bee,

The problem you're having is that your formula is testing the quoted
string,
not for the presence of a file.

To test for a file you need some vba. Here's a suitable function:

Application.Volatile

Function WkBkExists(WkBk As String)
If InStr(1, WkBk, "[") Then
WkBk = Left(WkBk, InStr(1, WkBk, "[") - 1)
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function

To use this function:
. open your Excel Workbook
. press Alt-F11 to open the vba editor
. insert a code module
. copy the code & paste it into your module
. press Alt-F11 again to return to the workbook
. code your formula as:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\[IT Daily Status 061208.xls]Executive
Summary'!$C$30),"\\PANTHER\depts\TechnicalServices \Daily Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)

Cheers

--
macropod
[MVP - Microsoft Word]


"El Bee" wrote in message
...

In the formula below I'm checking to see if a file exits. If it doesn't
then zero is suppose to be the value in the cell. If the file does
exists
then it's suppose to supply the value from a cell in one of the
worksheets
of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT Daily
Status 061208.xls]Executive Summary'!$C$30)

Formula Error
 

This time for sure ...

Function WkBkExists(WkBk As String)
Application.Volatile
If InStr(1, WkBk, "[") < 0 Then
WkBk = Replace(Left(WkBk, InStr(1, WkBk, "]") - 1), "[", "")
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function


Cheers
--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
And another correction. Sorry 'bout this. Here's the complete function:

Function WkBkExists(WkBk As String)
Application.Volatile
If InStr(1, WkBk, "[") < 0 Then
WkBk = Replace(Left(WkBk, InStr(1, WkBk, "]") - 1), "[", "")
End If
If Dir(WkBk, vbNormal) = "" Then
FileExists = False
Else
FileExists = True
End If
End Function

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi El Bee,

The problem you're having is that your formula is testing the quoted
string,
not for the presence of a file.

To test for a file you need some vba. Here's a suitable function:

Application.Volatile

Function WkBkExists(WkBk As String)
If InStr(1, WkBk, "[") Then
WkBk = Left(WkBk, InStr(1, WkBk, "[") - 1)
End If
If Dir(WkBk, vbNormal) = "" Then
WkBkExists = False
Else
WkBkExists = True
End If
End Function

To use this function:
. open your Excel Workbook
. press Alt-F11 to open the vba editor
. insert a code module
. copy the code & paste it into your module
. press Alt-F11 again to return to the workbook
. code your formula as:
=IF(WkBkExists('\\PANTHER\depts\TechnicalServices\ Daily Status
Report\History\[IT Daily Status 061208.xls]Executive
Summary'!$C$30),"\\PANTHER\depts\TechnicalServices \Daily Status
Report\History\[IT Daily Status 061208.xls]Executive Summary'!$C$30",0)

Cheers

--
macropod
[MVP - Microsoft Word]


"El Bee" wrote in message
...

In the formula below I'm checking to see if a file exits. If it
doesn't
then zero is suppose to be the value in the cell. If the file does
exists
then it's suppose to supply the value from a cell in one of the
worksheets
of
the file.
I keep getting the "#REF" error which then throws the rest of my
calculations into a tail spin. What am I doing wrong here?

=IF(ISERROR("\\PANTHER\depts\TechnicalServices\Dai ly Status
Report\History\[IT Daily Status 061208.xls]"),0,
'\\PANTHER\depts\TechnicalServices\Daily Status Report\History\[IT
Daily
Status 061208.xls]Executive Summary'!$C$30)