The problem I see is that the formula in each cell is different.
For example, the formula in:
Sheet1!E5 = "=IF(D5=0,"",RANK(D5,Sheet2!$A$2:$A$7,1))"
and the formula in:
Sheet1!E11 = "=IF(D11=0,"",RANK(D11,Sheet2!$B$2:$B$7,1))"

I plan to save those original formulae in Sheet3. Then if a user changes
Sheet1!E5 to "Ex" (which I allowed through data validation), and later
decides to delete the "Ex," the cell will acquire the formula from Sheet3!E5.
Similarly, if a user changes Sheet1!E11, the cell will acquire the formula
from Sheet3!E11, and so on.


"Allllen" wrote:

Hi Horatio

OK - I can see where you are going

What you need is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
'you can change that range a1:a10. this just means
'which area it should detect changes in.

If Target.Value < "Ex" Then
application.enableevents = false
'otherwise you generate a new event here and get
'into an endless cycle

Target.formula = "=[your formula]"
'you can figure out how to write this formula
'in the right way using record macro

application.enableevents = true
end if

End Sub




--
Allllen


"Horatio J. Bilge, Jr." wrote:

I did some testing, and I found the following code is a start. Now I need to
figure out how to include the entire range. For example, if the contents of
cell A4 are deleted, it is replaced with the formula in Sheet2!A4. Any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Target.Value = "=Sheet2!A1"
End If
End Sub



"Horatio J. Bilge, Jr." wrote:

That was the way I was trying to figure it out. It keeps me from losing the
formula, but the problem is getting the formula back into A1 if "Ex" is
deleted.

Maybe it could be done in a similar way with vba? If I copy the formula to
another sheet and protect it, then write the code to allow "Ex," and if it is
deleted, to insert the formula from the protected sheet.

It seems to make sense, but I'm not sure how to write it.

~ Horatio

"Allllen" wrote:

i think you already know how to do the validation bit.
what you ask could be done with vba programming but may be too complex

so i suggest using a combination of 2 cells:

A1 = first cell has validation and allows them to type "ex", maybe other
stuff too

second cell has an IF formula like this
=IF(A1="ex",A1,[your current formula])

or
=IF(A1<"",A1,[your current formula])

and you can protect the second cell to stop them overwriting your formula
--
Allllen


"JP" wrote:

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio