I don't want to use macros, because I don't want to include any VBA code in the workbook. I use conditional formulas to hide the error resulting from a missing .xla add-in.
Let me modify my question a bit.
First, here's my current code:
[vba]
Function rFormula(rngInput As Range, criteria As Boolean, Optional _
oLabel As variant = vbNullString, Optional nLabel As variant = _
vbNullString) As Variant
Application.Volatile False
Dim i As Integer
If Application.Evaluate(criteria) = True Then
Application.ScreenUpdating = False
For i = 1 To rngInput.Count
If Not rngInput(i) = vbNullString Then
rngInput(i).Replace "*", rngInput(i).Value
End If
Next i
rFormula = nLabel
Application.ScreenUpdating = True
Exit Function
End If
rFormula = oLabel
End Function
[/vba]
I use this to make an invoice, where the cells in column A contain the function =NOW(), those in column B = "ITEM", and each cell in column C contain my function:
A B C
1: =NOW() "Item" =rFormula(A1,B1<>"Item",,"Quantity")
2: =NOW() "Item" =rFormula(A2,B2<>"Item",,"Quantity")
3: =NOW() "Item" =rFormula(A3,B3<>"Item",,"Quantity")
.
.
.
Whenever an item name is typed in a cell in column B, that row gets a timestamp in column A to note when the item was ordered (=NOW() is replaced by the current value, and since the actual formula is removed, the date/time is persistent even when the workbook is saved and closed). The formula is eventually overwritten when a quantity is entered in column C. Since my function is saved as an add-in instead of a macro in the workbook, it can be used even on computers where macro security is set to high.
Here's my new question: is there any way to include the cell where the function is being called (i.e. application.caller.address or something similar) without getting a circular reference error, so that when the function is activated, it removes the function from itself (i.e. I don't have to worry about someone putting a quantity in column C to remove the function)? I was playing with private functions and subs, but can't seem to do this. Any thoughts from anyone?
Thanks!