How to change a single character in an excel formula for 1500 rows

How to change a single character in an excel formula for 1500 rows

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.

Solutions/Answers:

Answer 1:

There are several ways to do this

  1. 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 columns("A:A") instead of usedrange

    Sub replace()
     Application.DisplayAlerts = False
     For Each cell In UsedRange
        cell.Formula = replace(cell.Formula, "='4", "='3")
     Next
     Application.DisplayAlerts = True
    End Sub
    
  2. Use Excel find & replace function » CTRL+H and search in formulas.
    Select your columns you want to alter before you open the dialog

    enter image description here

Answer 2:

You can select the cells you’d like to adjust and use Ctrl+H to replace 3 with 4.
Since the formula has no other 3s, this easy trick should work.

Answer 3:

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..

Related:  Read MS Excel XML file to pandas dataframe?

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)

Our Awesome Tools

References