Excel: remove #VALUE formula result

If a cell shows #VALUE it is because it cannot evaluate what you asked for.
eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together. 

=IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))

So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
Thus if there is an error, the formula returns "No Data", otherwise you get the sum.

Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.

Hope this helps

MS-Access: How to refer to subform control in SQL

Forms![main form name]![subform control name].Form![control name]


To refer to a control on a subform, use the following syntax: 

   Forms![main form name]![subform control name].Form![control name]
    

To refer to a control on a subreport, use the following syntax: 

   Reports![main report name]![subreport control name].Report![control
   name]
    

NOTE: Only subforms are discussed in the rest of this article, but all the information applies to both subforms and subreports. 

It is important to note that you cannot refer to controls on a subform with the following syntax: 

   Forms![subform name]![control name]
    

Private Sub Command3_Click()
    On Error GoTo err_Command3_Click

    Dim intHRGroupID As Integer
    Dim sql As String
    
        Debug.Print "############  begin   ##################'"
    
    Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].SetFocus
    HRGroupID = Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].Text
    
    sql = "UPDATE [HR_TLV_HR_Staff_DB] z INNER JOIN ([HR Group to HR Mgr Relationships] x INNER JOIN [HR Manager Group] y ON x.[HR Manager Group ID] = y.[HR Manager Group Code] " & vbCrLf & _
          "    ) ON z.[ID] = x.[HR Manager Emplid] " & vbCrLf & _
          "SET x.[Included in Group] = 1 " & vbCrLf & _
          "WHERE y.[HR Manager Group Code] = " & HRGroupID & vbCrLf & _
          "  AND " & Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form.Filter & " AND 1 = 1"
    
    Debug.Print sql
        
    sql = Replace(sql, "[HR Mgr to Group Update]", "z")
    
    Debug.Print "############  revised   ##################'" & vbCrLf
    Debug.Print sql
    
    DoCmd.RunSQL sql

exit_Command3_Click:
    Exit Sub
err_Command3_Click:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Command3_Click
End Sub

How to Return the First or Last Match in an Array

http://support.microsoft.com/kb/214069

You can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it returns the last match encountered. This behavior is true for the VLOOKUP(), HLOOKUP(), and LOOKUP() functions. 

To find the first value instead of the last value in an array, use the INDEX() and MATCH() functions.

=IF(INDEX('CMS LILO DATA'!$AA$2:$AA$500, MATCH(A4, 'CMS LILO DATA'!$A$2:$A$500,0),1)=1,1,"")


=IF(
        INDEX('Sheet1'!$A$2:$A$500, MATCH(A4, 'Sheet2'!$A$2:$A$500,0),1)
           =1,1,"")