Indirect function fails - Why ?

[krissz]

I have the following function in a worksheet. This is repeated multiple time. It works.

=IF(ISNA(VLOOKUP($C14,'PL 2'!$B$2:$Y$128,5,)),0,VLOOKUP($C14,'PL 2'!$B$2:$Y$128,5,))*R$2

Each column refers to a different sheet, from "PL 1" to "PL 50" at present.
I have tried to make it more general, eliminate possible errors and thus simplify extension of the application when adding further columns by using Indirect. The new function is below, and cell R10 = 2:

=INDIRECT("IF(ISNA(VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)),0,VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,))*R$2")

This comes up with a #REF! error.
Why ?
I have carefully checked & the two functions are identical. I then tried, with the same failure:

=IF(ISNA(INDIRECT("VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)")),0,INDIRECT("VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)"))


Also, the following works perfectly:

=IF(ISERROR(INDIRECT("'PL "&R$10&"'!"&"$B3")),"",INDIRECT("'PL "&R$10&"'!"&"$B3"))

 

[AdamL]

Hi

INDIRECT can only process cell and range references I believe, not formulae.

 

[texasalynn]

ok go back to basics - try each formula on it's own

 

[krissz]

Thank you.
I have checked & I believe Adam is right. I have checked independant parts as below, and these also produce #REF!.

=INDIRECT("VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)")

and

=INDIRECT("IF(ISNA(VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)),0,1)")

The only other solution would seem to be to write a custom function - it is used over 7000 times in the present application. The look-up range on the other sheets is constant and the other values are always on the same row or column as the equation. The only values that needs to be passed on to the function are the current Row & Column.
Any thoughts ?

 

[AdamL]

=IF(ISNA(INDIRECT("VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)")),0,INDIRECT("VLOOKUP($C14,'PL "&R$10&"'!$B$2:$Y$128,5,)"))

Also, the following works perfectly:

=IF(ISERROR(INDIRECT("'PL "&R$10&"'!"&"$B3")),"",INDIRECT("'PL "&R$10&"'!"&"$B3"))

Have you tried:

=IF(ISNA(VLOOKUP($C14,INDIRECT("'PL "&R$10&"'!$B$2:$Y$128"),5,)),0,VLOOKUP($C14,INDIRECT("'PL "&R$10&"'!$B$2:$Y$128"),5,))

or in Excel 2007+

=IFERROR(VLOOKUP($C14,INDIRECT("'PL "&R$10&"'!$B$2:$Y$128"),5,),0)

 

[krissz]

Adam
Thank you - works perfectly.
Had started on a UDF, which I may finish later as it eliminated the problem of errors if the lookup function fails.
If it works, will post it for information.

 

[krissz]

This is the UDF I have written, but am getting a "Compile Error: Object required" which I do not understand (the line in red) as this section of code I use in several routines. Is there any difference between a Routine & a Function ? Or am I missing something else ?

Function Get_Qty(G_Row As Integer, G_Col As Integer) As Integer


      Dim G_PL_No As Integer
      Dim G_Sheet As Worksheet
      Dim G_Pn As String

      Dim R_Pn_NF As Integer
      Dim Test_R As Range
      Dim R_PnV As Integer
      Dim Pn_NF As Integer
      Dim Pn_Found As Boolean
      
      G_PL_No = Sheets("Master").Cells(G_Col & "10").Value
      G_Sheet = "PL " & G_PL_No
      G_Pn = Sheets("master").Range("C" & G_Row).Value
      
      Get_Qty = 0
      Sheets(G_Sheet).Select
      Range("A1").Select
    
      ' Check for any data.  Exit if blank
      R_Pn_NF = 0
      Set Test_R = Range("F1:F20")
      If Application.WorksheetFunction.CountA(Test_R) = 0 Then Exit Function
        
      'Find Part No-Value Row
      R_PnV = Application.WorksheetFunction.Match("Part No-Value", Range("B1:B50"), 0) + 1
      Range("D" & R_PnV).Select
    
      ' Check if the Part No is used.  Exit if not used
      With Range("B" & R_PnV + 1 & ":B500")
            Set [I][B][COLOR=Red]Pn_NF =[/COLOR][/B][/I] Cells.Find(G_Pn, after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)
            If Not Pn_NF Is Nothing Then Pn_Found = True
      End With
      If Not Pn_Found Then Exit Function

      Get_Qty = Range("B" & R_PnV + Pn_NF).Offset(0, 4).Value

End Function

 

[krissz]

The function as modified below compiles and, when called from a test sub with the applicable values [ t_val = Get_Qty(23, 18) for cell R23 ] it works perfectly - returns a value 1 (or whatever I enter in the applicable cell). However, when used in cell R23, the function

 =Get_Qty(ROW(),COLUMN())

returns ZERO.
The code is below - what have I done wrong now ?

Function Get_Qty(G_Row As Long, G_Col As Long) As Integer

      Dim G_PL_No  As Integer
      Dim G_Sheet  As String
      Dim G_Pn As String

      Dim R_Pn_NF As Integer
      Dim Test_R As Range
      Dim R_PnV As Integer
      Dim Pn_NF As Integer
      
      On Error Resume Next
      
      G_PL_No = Sheets("Master").Cells("10", G_Col).Value
      G_Sheet = "PL " & G_PL_No
      G_Pn = Sheets("master").Range("C" & G_Row).Value
      
      Get_Qty = 0
      Sheets(G_Sheet).Select
    
      ' Check for any data.  Exit if blank
      Set Test_R = Range("F1:F20")
      If Application.WorksheetFunction.CountA(Test_R) = 0 Then Exit Function
        
      'Find Part No-Value Row.  I only search after this row
      R_PnV = Application.WorksheetFunction.Match("Part No-Value", Range("B1:B50"), 0) + 1
    
      ' Check if the Part No is used.  Exit if not used
      Set Test_R = Range("B" & R_PnV + 1 & ":B500")
      Pn_NF = Application.WorksheetFunction.Match(G_Pn, Test_R, 0)
      If Pn_NF = 0 Then Exit Function
      Get_Qty = Range("B" & R_PnV + Pn_NF).Offset(0, 4).Value

      Sheets("Master").Select

End Function

 

[texasalynn]

I would suggest that you put this as a new post since it is a different question

 

[krissz]

Thank you everybody.
I have resolved the function problem, but as it takes so very long to run (as opposed to the inbuilt functions) that I am abandoning it.
The overall function may be long & complicated, but at least it calculates within a few seconds - rather than in 10's of minutes.

Might re-post anyway to see if anything interesting arises.