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