| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Formula Error | New Users to Excel | |||
| Formula Recalculation Error | Excel Worksheet Functions | |||
| Let me save/close EXCEL if a formula I put in has an error in it | Setting up and Configuration of Excel | |||
| I get error with "ROWS" in the formula - nested formula question | Excel Worksheet Functions | |||
Hybrid Mode
