Function to Assign a Value to a Different Cell

  • Thread starter Thread starter Mike Kramer
  • Start date Start date
M

Mike Kramer

I am looking for a function (not a macro) that assigns a
value to another cell. This allows me the options to a)
automatically fill a cell if a condition is met and b)
manually fill the cell if the condition is not met,
without overwriting any function.

The function would look something like the example below,
where every recalculation of the spreadsheet the cell D4
is assigned the value 3 if the condition is met.

B2: if(condition,Mike(3,D4))

Cheers,
Mike.

PS
Obviously the example formula results in #NAME? error as
Excel does not recognise my name as a valid function and
can not guess what I am trying to do today.
 
Mike,

You could use an IF function in D4. The one below would return the value 3
when the condition(s) is met, and something else when not. You would have to
put your condition-not-met value elsewhere (I'll use E2), and let the IF
function pick it up and return it in D4 under the condition-not-met
condition.. In neither case would you type anything into D4; that would
overwrite the formula.

=IF(condition, 3, E2)

If you insist on the 3 being plugged into D4, an event macro will have to do
it.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
....Code to test condition...
If Condition Then Sheets("Sheet1").Range("D4") = 3
End Sub

Earl Kiosterud
mvpearl omitthisword at verizon period net
 
Back
Top