Adjust formula to allow for changing sheet reference.

I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.

=IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"")

I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?

=IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"")

The sheet reference will change from '1' through '90'.

One way would be to add a macro that can insert formulas into each row based on the the correct sheet name reference, however you want the macro to determine that. The main goal is to have normal direct formulas in the cells.

If we're going to go the macro route, there's no reason not to let the macro simply collate your report for you with no formulas. You simply want the data from row1 on every sheet collated into a report on sheet1? That's probably easiest of all.

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, NR As Long
Application.ScreenUpdating = False
Cells.Clear
NR = 1

    For Each ws In Worksheets
        If ws.Name <> Me.Name Then
            Cells(NR, "A") = ws.Name
            ws.Range("B1:H1").Copy Cells(NR, "B")
            NR = NR + 1
        End If
    Next ws
    
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Just delete the macro you were using. Then open the SHEET MODULE for this sheet and insert the macro above into the sheet module. This will cause the macro to run itself every time you bring the sheet up onscreen...which makes it as effective as =A1 formulas.

In case you don't know...the sheet module is listed above the regular modules, open the actual module with the sheetname that is your 'report'. Another way to open it is to right-click on the sheet tab and select VIEW CODE.

So row 2 is all you need? There's no row 3 formulas, and row 4?

This standalone macro goes in a standard module. Run it and will create formulas in row2 based on the sheet names found in row1. They don't have to be 1,2,3...etc. They can be anything. You'll get a normal REF# error if the value in row1 doesn't match an existing sheet.

Option Explicit

Sub AddFormulas()
'JBeaucaire  (11/22/2009)
Dim LC As Long, i As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To LC
    Cells(2, i).Formula = "='" & Cells(1, i) & "'!A1"
    'add more rows here to add more formulas in the following rows...
Next i
    
Cells.Columns.AutoFit
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

You can duplicate the syntax in the red line of code to add more formulas to get data from other cells on each sheet.