IF(C20="-",12.36,input)

[youngman]

hi,
i wonder how to describe such a cell with "if".

=IF(C20="-",12.36,input),i want the part of"input" to be inputed with
keyboard.

but if i input with keyboard the "if*****" will dispear .

how to solve this issue.

thank you.

 

[Intruder9]

Why don't you just refer it to another cell for input such as
=IF(C20="-",12.36,A1),

 

[youngman]

THANKS$B!!(BFOR$B!!(BYOUR$B!!(BADVICE.
but it is just a partial solution.
anything better?

thanks

 

[Dave Peterson]

I don't think you're going to get what you want with a formula.

Once the user overwrites the formula, they can go back to C20, change it to "-"
and you don't have the formula around to get updated.

But you could use an event macro. This can look for changes to C20. When it
sees a change, it can check to see what's in that cell and take appropriate
action.

I'm gonna use A1 as the cell that would have gotten the formula in my example
(change it to suit your data).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

Set myCell = Range("A1") '<--- change here!

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("c20")) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target
If .Value = "-" Then
myCell.Value = 12.36
Else
myCell.Value = InputBox("Enter something to go into cell: " _
& myCell.Address(0, 0))
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should behave this way. Select View Code
and paste this into the code window.

Go back to C20 and try it out. (Don't forget to change A1 to the correct cell.)

 

[Dave Peterson]

Oops. I left out a line that might be important:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

Set myCell = Range("A1") '<--- change here!

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("c20")) Is Nothing Then Exit Sub

on error goto errHandler: '<---added this line.
Application.EnableEvents = False
With Target
If .Value = "-" Then
myCell.Value = 12.36
Else
myCell.Value = InputBox("Enter something to go into cell: " _
& myCell.Address(0, 0))
End If
End With

errHandler:
Application.EnableEvents = True

End Sub