I’m trying to write a custom function that tests the value in one cell and if true references the value in another. I can do it for one cell using the “activecell” in the function but if I copy the formula down to other cells the value of the referred cell changes based on the location of the activecell.
The custom function defined in the attached XL file is as follows:
Function New1(Amount)
If Amount = 4 Then
New1 = ActiveCell.Offset(0, -1).Value
End If
End Function