Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

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

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Try

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

    and copy down

  5. #5
    Roger I m ginna try again sending you my template file zipped
    Attached Files Attached Files

  6. #6
    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?

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •