I have a 2024 24-inch iMac running 26.2. I am building a spreadsheet with a summary sheet using data from other sheets. I built one column of formulas and would like to be able to copy those formulas to another column and then change a reference in all the cells in that column.
For example, "Jun::Table 1::C4$4", change "Jun" to "Jul" in all the formulas.
I tried to copy a segment of the page as an example, but it didn't work.
iMac 24″, macOS 26.2
There is no good built-in way to do it. One at a time is tedious. SGIII wrote a script (now a shortcut) for doing find-replace in a selected range of cells. It was written for data cells only, not formulas. I thought he had one for formulas, too, but I couldn't find it so I modified the other one to allow find-replace in formulas. If you use shortcuts, you can use the link below to install it. It is really just an AppleScript, which can be used in Script Editor or turned into a quick action / service.
https://www.icloud.com/shortcuts/7e030d36e10349f8a7f8a3d0b0a01851
If there are any other instances of "Jun" in your formulas that are not referring to the sheet name, you might want to include the "::" (i.e., replace "Jun::" with "Jul::") to be sure you don't accidentally replace other occurrences of the letters "Jun".
Also, this script changes each cell individually. If you changed a lot of cells and need to undo, you will have to hit Undo a lot of times.
There is no good built-in way to do it. One at a time is tedious. SGIII wrote a script (now a shortcut) for doing find-replace in a selected range of cells. It was written for data cells only, not formulas. I thought he had one for formulas, too, but I couldn't find it so I modified the other one to allow find-replace in formulas. If you use shortcuts, you can use the link below to install it. It is really just an AppleScript, which can be used in Script Editor or turned into a quick action / service.
https://www.icloud.com/shortcuts/7e030d36e10349f8a7f8a3d0b0a01851
If there are any other instances of "Jun" in your formulas that are not referring to the sheet name, you might want to include the "::" (i.e., replace "Jun::" with "Jul::") to be sure you don't accidentally replace other occurrences of the letters "Jun".
Also, this script changes each cell individually. If you changed a lot of cells and need to undo, you will have to hit Undo a lot of times.
Double-click on one of the formulas to edit, then click on fx at the left of the editing window. Select Display formula as text, and edit.
Hopefully, you don't have too many to do like this.
I have 55 rows to change, if I can't find a better way, then this will be it. Thanks
That worked like a champ! What a useful script, that saved me a lot of time and potential mistakes. Thank you, problem solved!.
Changing copied spreadsheet formulas on iMac
