It would be very useful to have the possibility to search on Validity criteria formulas (Search, custom, etc.) in the Find & Replace dialog, in addition to Formulas, Values and Comments.
Personally I'm not sure it's needed Let's ask other opinions, Ux-team?
Validation is a function to check the input and not some data-inherent information. But I can follow the use case that on a sheet some cells have restrictions and you want to check it. Could imagine a special color for View > Value Highlighting.
FWIW, IMHO, Validity (aka Validation), Custom Condition format, and any other place in which formulas could be used, should be at least considered as an alternative addition to the areas in which F&R could be optionally searching on. OTOH, there could be a misinterpretation of the meaning/intention of these items. ATM, the items "Formulas" and "Values" refer to either the cell's formula, or their results, respectively. But then, if there are other items such as Validity (or CF for example), what would be the search acting on? For example, in the case of a Validity > List, you would be searching on the content of the list. But if we have Validity > Custom, using a formula, would the search be performed on the custom formula, or instead, on its result? Or both? Or, should there be two separate items to search on, "Validity (formula)", and "Validity (values)"? BTW, I mentioned a similar feature as a side-note in the ticket requesting an "All" item (in addition to "formulas", "values" and "comments").
If we really consider F&R, the dialog should allow to replace the validation. That's probably not wanted.
(In reply to Heiko Tietze from comment #4) > If we really consider F&R, the dialog should allow to replace the > validation. That's probably not wanted. Please allow me to ask... Why exactly would that be not wanted? Could it be problematic for a user that makes the replacement by mistake? Yes. But if you want to replace something in every formula in cells, couldn't that be also a possible case for Validity, or for CF? And then there is the case of simple values. Let's assume that you want to F&R the content of every cell that contains some string. This could also be the case for Validity content; is it not? Perhaps the addition of items should be under the "Other options" section, and even with some colored warning (to reduce mistakes). But that does not mean that the possibility of searching (and optionally replacing) is not wanted (at all). I would suggest asking other users whether this possibility was ever needed for them.
(In reply to Heiko Tietze from comment #2) > Validation is a function to check the input and not some data-inherent > information. But I can follow the use case that on a sheet some cells have > restrictions and you want to check it. Could imagine a special color for > View > Value Highlighting. That would be to somehow mark/find/list which cells have some kind of Validity check (or some kind of CF). That would also be a useful feature, independent of the F&R request in this ticket.
We discussed the topic in the design meeting. First of all it is unclear what exactly F&R should do (comment 3). If the functionality is supposed to work on every aspect, the only reasonable way to implement this is to introduce a validation object similar to styles and run F&R on this object. If the use case is to identify cells with validation we may use the value highlight feature, with the drawback of concurrent information like being a number and a validation, or per indicator on the cell similar to comments. What is your goal, Rertini?
I'll try to explain: In several cells I use cell range formulas validation to select from a list. In case of any change in any part of the spreadsheet that affects these lists, it would be convenient to be able to search for a pattern within these validation formulas, and then make the modifications manually. I agree that it is not convenient to do automatic search and replace. I would just like to be able to identify which cells have a validation formula that requires modification, without having to go into the validation of each one to see which one needs a change. I suggested the Find and Replace dialog since you can search through cell formulas and comments there.
(In reply to rertini from comment #8) > I'll try to explain: > In several cells I use cell range formulas validation to select from a list. Can you pls tell if you use a cell range with cell addresses or a named range? > In case of any change in any part of the spreadsheet that affects these A named range is much less influenced by changes in the spreadsheet..
> Can you pls tell if you use a cell range with cell addresses or a named range? Both, but I think it is irrelevant. Suppose you have a custom function defined, it's easy to find out the cells formulas using that function, but to check if the function is in use on a cell validation you have to check them une by one.