In the past it was trivial to edit formula - you didn't even need to "Show Formulas" (I have been building spreadsheets over four decades, since well before Microsoft introduced Office (Word, Excel, etc.).

- Background (not necessary to read this, but some may find it helpful to understand what I am doing...) I am pulling data from an unstructured data souce with variable number of items per record but I need to process the data in fixed arrays. Anyway, I have parsed each record into a tab "1" "2" "3" and so on.

The sheet I am creating is full of matrix arrays of data and the data is fetched from arrays of data in the worksheet tabs '1' '2' '3' etc. - so there is a massive duplication of arrays of formulae.

I am analyising the data from each record and cross adding for each record.

In each repetition of these arrays I am refrencing the same cell coordinates (such as B3, B4, B5 ... C3, C4, C5 ... etc.

However, they are fetched from different tabs and so after I copy an array, I need to search and replace the worksheet tab names.

So, for example, I need to do search and replace for a block (9 x 16) - 144 elements):

=IF('1'!B$3="","n/a",'1'!B$3)   ...  =IF('1'!J$3="","n/a",'1'!J$3)
           ...                                   ...
=IF('1'!B$18="","n/a",'1'!B$18) ...  =IF('1'!J$18="","n/a",'1'!J$18)

There are sixteen such blocks (actually 32, for weighting)

If I copy this array and paste 15 times (making 16 identical arrays, each of 144 cells = 2,304 cells.

So, for the second block, I want to do a global search and replace of the part of the forulae that calls up sub-worksheet 2, 3, 4, 5 ... 16 for each of the 16 blocks.

So, the string I am searching for is '1' - to be replaced by '2' for the second block - by '3' for the third block, and so on.

I have done this kind of mass replacement of elements within a formula - but this does not seem to be possible anymore - not at least any method I have tried so far.

In the past it was possible to do this without clicking on [Show Formulas] - but I have tried to do it without showing formulas and when showing formulas - but it makes no difference - I always get the same response:

• We couldn't find anything to replace. Click Options for more ways to search.

This spreadsheet is not protected in any way.

Can anyone suggest a soliution to replace '1' with (say) '9' in the above arrays of formulae?
🤔
~~