Automation from Macro

[mole999]

I have a list of sheet names in Col A of INDEX, each of which is preceded with a ',
there are 37 unique IDs

I want a SUM of cells AN2:AN600 from each of the sheets as

=SUM('NC (119 of 510)'!$AN$2:$AN$600)

I can create these manual

I'm trying to concoct a formula that will combine the sheet name and range to then be able to drag down, ultimately it will be in a macro to automate this step

can someone point me

 

[steve the fish]

So you want a formula or VBA? A formula for each sheet or a total? Sounds like you want INDIRECT if you want a formula.

 

[mole999]

Agree that INDIRECT is the way to go, I just can't get it to assemble as a drag down using column A (because the numbers change I can't just construct something simple it has to relate to the Tab name and the Index name (minus the '), everything I have tried throws an error and I can't hit the right construct

 

[steve the fish]

So the tabs are all called 'NC (Something of Something)'? What do you have in Col A of the index sheet?

 

[mole999]

Column A is the names of the Tabs

its a index built after each sheet is added from

Name the Sheets, from a loop

Selector = "NC"

...CODE..............................

    Dim LastRow2 As Long
    LastRow2 = ActiveSheet.Range("A10000").End(xlUp).Row
Dim SUMMER As Long

    SUMMER = ActiveSheet.Range("A1")
    ActiveSheet.Name = Selector & " (" & SUMMER & " of " & LastRow2 & ")"
           
    Sheets(1).Select

so they are an exact match

Then, Create the INDEX

Private Sub IndexIt()
    Dim WS As Worksheet, WsInd As Worksheet, lStartRow%, lStartCol, sBackRange As String
    '##1: Where should the back-to-index-page link be, change if necessary
    sBackRange = "A1"
    
    '  ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
   
    'Set Ws = Worksheets("INDEX")
    Set WS = Worksheets.Add(Before:=Worksheets(1))
    ActiveSheet.Name = "INDEX"
     
    lStartRow = Selection.Row
    lStartCol = Selection.Column
     
    Set WsInd = ActiveSheet
     
    'Add the links
    For Each WS In Worksheets
        If WS.Name <> WsInd.Name Then
            WsInd.Hyperlinks.Add WsInd.Cells(lStartRow, lStartCol), "", "'" & WS.Name & "'!A1"
            WsInd.Cells(lStartRow, lStartCol).Value = WS.Name
            lStartRow = lStartRow + 1
             
            '##2: Add link back to index, comment the following 2 lines if you don't want this part
            WS.Hyperlinks.Add WS.Range(sBackRange), "", "'" & WsInd.Name & "'" & "!A1"
            WS.Range(sBackRange).Value = "Back to Index"
        End If
    Next WS
     
    WsInd.Activate
    
    Sheets("INDEX").Select
    percentages
End Sub

 

[steve the fish]

So it should be this if im understanding:

=SUM(INDIRECT("'"&A1&"'!$AN$2:$AN$600"))

And just drag it down.

 

[mole999]

That's it exactly, I think I was missing a speech mark, and couldn't resolve it