Dynamic worksheet name in formular

Question

I have an Excel workbook with 101 worksheets. The 1st worksheet shall provide me with a reference of cell A1 of all following 100 worksheets. Which formula can be used without manually linking each worksheet?

I have not current solution.

Answer 1

You're looking for the Indirect() function.

If the cell B6 has a sheet name, then the formula =INDIRECT("'"&B6&"'!A1") will return the value from A1 in that sheet. (It won't copy any formatting, just the value.)

If you need a list of all the sheets names in your large workbook, VBA can handle that. Add this function to a module and run it to dump all the sheet names in column A of the active sheet. NOTE: This will overwrite anything you already have in those cells so make sure the active sheet is a blank one.

Sub dumpSheetNamesToActiveSheet()
    For r = 1 To ThisWorkbook.Sheets.Count
        ActiveSheet.Cells(r, 1) = ThisWorkbook.Sheets(r).Name
    Next
End Sub