How to modify (find&replace) validity source's fields in many cells at once?

Maciej_M · November 17, 2023, 10:14am

My spreadscheet has many cells with validy sources filled for ex. in followin way:

Cell Validy source’s field
B1 MATCH($A$1;LookupArrayOLD;0)
B2 MATCH($A$2;LookupArrayOLD;0)
B3 MATCH($A$3;LookupArrayOLD;0)
…

How to find and replace particular string in every searched cell’s data-validity source , as it is possible
with Find&Replace function (Ctrl+H) for Formulas, Values, Comments ?

Such that it’ll become:

Cell Modified Validy source’s field
B1 MATCH($A$1;LookupArrayNEW;0)
B2 MATCH($A$2;LookupArrayNEW;0)
B3 MATCH($A$3;LookupArrayNEW;0)
…

karolus · November 17, 2023, 10:58am

Hallo

IMHO it doesnt make sense to put a formula into →Data-Validation which give the option to choose exactly one number

Maciej_M · November 17, 2023, 11:23am

It was example - a small piece of very extensive formula putted in many data validation’s fields.
I’ll try to put this way:
got:

Cell Validy source’s field
B1 A1old
B2 A2old
B3 A3old
…
want:

Cell Validy source’s field
B1 A1new
B2 A2new
B3 A3new
…

is it possible to do in LIbre (or other) -Office ?

karolus · November 17, 2023, 11:32am

Hallo

of course you need to change the $absolute Rowreference to an relative Rowreference

B1 MATCH($A1;LookupArrayNEW;0)

Change →Data→Validity in B1 let B1 selected and use the »Format-Brush« to clone the Validation onto:
B2 , B3 … ectpp.

Maciej_M · November 17, 2023, 4:59pm

It did trick for me. Together with a tip about “relative Rowreference”. Thank You!

Still wonder , why possibility to modify mentioned field, doesn’t reside on “Find Replace” window, in neighborhood of “Formulas,Values and Comments”.