XLM4.0 Has Formula in Validation

[Jon von der Heyden]

Hi All

I was trying to see if I could come up with a slick solution to this post.

Using names - e.g. Has_Formula refers to:
=GET.CELL(48,INDIRECT(ROW(),COLUMN(),4))

This should return TRUE if the cell houses a formula, FALSE if it does not.

Now, I have attempted to apply this within validation to stop a user from entering a constant into a cell but it doesn't work (i.e. it does nothing).

E.g: In A1 validation =(Has_formula=TRUE)

I'm intrigued, why doesn't this stop a user from inputting a constant?

Edit: Furthermore I have learned that this does work on conditional format. Bizarre?

Best regards
Jon

 

[JimB]

I have no idea why that validation approach does not work - but how about this...

While it does not validate just the cells containing formulas, you can select the entire worksheet and enter this Data Validation formula:

=CELL("protect",A1)=0

This will allow you to prevent changes to all locked cells, even if the sheet is not protected. Just run through the worksheet and unlock the cells you will allow the user to change. Certainly not foolproof, but perhaps enough to protect your formulas from the casual Excel user.