Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.