Introduce Value in a cell with formula, option

[Guest]

I have a column with formulas in cells, but i want sometimes to introduce
values instead having the result of the formula. At the same time i don't
want to loose the formula in the cell. Perhaps a condition that cleans the
formulas and put cells blank?
Thanks!

 

[Bob Phillips]

You can't have both. A cell can either have a formula, and displays the
result of that formula, or a value.

You could perhaps change the formula to identify when that value should be
there and return that. For instance, instead of

=SUM(A1:A20)

=IF(B1<>"","",SUM(A1:A20))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

You can have a formula or a value.........not both.

Typing a value will overwrite the formula.

Perhaps what you want could be achieved through a formula.

=IF(SUM(A1:A10)<>1234,5678,SUM(A1:A10))


Gord Dibben MS Excel MVP

 

[Guest]

if i make a list, in the validation of data, that one of the entrie is the
formula, it could result. I've tried but the formula doesn't display. Can it
be possible?

 

[Gord Dibben]

You could do that using DV and sheet event code.

Assume you have a DV drop-down in B3 with 1, 2, 3, 4, 5 ,6 to choose from.

Choose 4 and the event code will run placing a formula in B3.

Any other number chosen will wipe the formula.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
If Target.Address = "$B$3" And Target.Value = "4" Then
Application.EnableEvents = False
Target.Formula = "=SUM(A2:A10)"
End If
endit:
Application.EnableEvents = True
End Sub


Gord