Batch Replace Cells Formulas

Question

Level 1

12 points

Batch Replace Cells Formulas

I have 26 sheets in my Numbers documents. The sheets are names c01, c02, c03….c26. The sheets are exactly the same and represent a given time period with hundreds of entires per sheet. Finally, I have one summary sheet linking back to the 26 sheets.

On the Summary sheet, each row links to a sheet and extracts 20+ data points. Example: row 2 links to sheet c01 with a formula to pull in various information (e.g., c01::$E$8, c01::$G$75, etc.). Row 3 will link to sheet c02. The same data points are on each sheet.

I have row 2 on my Summary sheet set with all of its formulas to sheet c01. I would like to copy row 2 to row 3, then change the formula from c01::$E$8 to c02::$E$8 without having to edit each cell’s formula?

I tried to highlight row 3 and use Find & Replace. But, I can’t get that to work.

Is there a way to “batch” change a row's formula replacing all c01 entries with c02?

Current Pro Desktops

Posted on Jan 26, 2022 11:58 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,432 points

Jan 26, 2022 12:27 PM in response to Bear34_1

You can do this with the INDIRECT formula. An example is shown below.

INDIRECT takes a string that looks like a cell reference such as "CO1::Table 1::E8" and turns it into a cell reference. With the table names in the header column and the cell addresses in the header row, you can put this formula into the first cell (B2 in this example) and copy/paste or fill it to all the others.

I put IFERROR around it so that you can pre-fill the sheet names into the table before the sheets actually exist. I set the "iferror" result to the null string "" but you might prefer it be a zero. With the sheet names pre-filled into the table, when you create a new sheet it will automatically be included. As you can see, there is no sheet named CO4 in my document.

Often this kind of table is an intermediate table used to collect the data into one place. The actual "summary" table would get its data from this table rather than directly from each sheet.

Reply