I have over 1500 rows of information with formulas such as, ='3'!$AH$6 , where each column contains information from another sheet within the workbook. In the example, the sheet labeled '3' is referenced. Is there a way to change only the reference to another sheet for all 1500 rows at one time? For example change the formula to ='4'!$AH$6. I only need to change the reference to another sheet as the cell references will stay the same. I hope this makes sense, thanks for any help.
There are several ways to do this
Use VBA » ALT+F11 to insert and ALT+F8 to execute this little macro
This has the benefit that you can insert even invalid references (e.g. the reference will be there only on another PC or in the future) without a update data dialog for every cell
If you want, you can select single columns with
Sub replace() Application.DisplayAlerts = False For Each cell In UsedRange cell.Formula = replace(cell.Formula, "='4", "='3") Next Application.DisplayAlerts = True End Sub
Use Excel find & replace function » CTRL+H and search in formulas.
Select your columns you want to alter before you open the dialog
Just erase the Cell data on all rows, edit just the first line and then copy the formula over the rows pulling the cross, on the bottom right corner of the cells..
You edit the first cell, then pull a cros of this cell, located at the right bottom corner, pull it to the row you want (in case, pull it down to the row 1500)