How do i quickly change a bunch of formulas that have links to other sheetsvin sequen

[Silvia]

I have in tab"summary" in a1 aformula that says ='1'!$l$10
then i have in a2 aformula that says ='2'!$l$10, in a3 ='3'!$l$10 and so on for 100 rows how can i change that quickly ? I have the same issue in column b c d e f.
I already tried the find aand replce but even if it saves some time you still have to repeate it 100 rows.
please help!
thx
silvia

[Roger Govier]

What part of the formula are you trying to change?
Give an example of the existing formula and the new formula for rows 2, 3 and 4 for columns a,b,c,d,e
or better still, upload a copy of your workbook with a complete explanation of what you are trying to achieve.

[Silvia]

I tried uploading the excel file but i had troubles maybe the server was not working or i did spmething wrong. Will try again

[Bob Phillips]

Try

=INDIRECT(ROW(A1)&"!I10")

and copy down

[Silvia]

Roger I m ginna try again sending you my template file zipped

[Silvia]

This works if u do not rename the sheets but this template is used by my sales people the have to rename the sheets with the name of the project they want to register. So the formula has to work also whenbthe sheets are renamed differently. You can check my zipped spreadsheet tha i posted. Any suggestions to overcome this issue?

[Bob Phillips]

Public Sub AddFormulae()Dim ws As Worksheet
Dim rowidx As Long


    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With ActiveWorkbook
    
        rowidx = 8
        For Each ws In .Worksheets
        
            If IsError(Application.Match(ws.Name, Array("Instructions", " Name", "Misc. Projects", "Summary"), 0)) Then
            
                rowidx = rowidx + 1
                .Worksheets("Summary").Cells(rowidx, "B").Formula = "='" & ws.Name & "'!$C$11"
                .Worksheets("Summary").Cells(rowidx, "C").Formula = "='" & ws.Name & "'!$C$12"
                .Worksheets("Summary").Cells(rowidx, "D").Formula = "='" & ws.Name & "'!$K$39"
                .Worksheets("Summary").Cells(rowidx, "E").Formula = "=IF($D" & rowidx & "=""Y"",'" & ws.Name & "'!$K$40,'" & ws.Name & "'!$C$39)"
                .Worksheets("Summary").Cells(rowidx, "F").Formula = "='" & ws.Name & "'!$A$40"
                .Worksheets("Summary").Cells(rowidx, "G").Formula = "='" & ws.Name & "'!$A$62"
                .Worksheets("Summary").Cells(rowidx, "H").Formula = "='" & ws.Name & "'!$P$33"
            End If
        Next ws
    End With


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub