Using a cells contents as part of an address in a function

Hello,
I've been banging my head against a wall trying to do a seemingly simple task in excel. Basically I have a workbook with many worksheets (sequentially named "ARC001", "ARC002, "ARC003", etc). On each worksheet in cell C2 is the title of the worksheet.

I'd like to create a "table of contents" worksheet that would pull the title from each worksheet (cell C2).

To do this by hand, I understand I can manually type

Please Login or Register  to view this content.

in the first cell and then

Please Login or Register  to view this content.

into the next cell, and so on, but I have almost 75 worksheets and I am of the belief that there must be a way to do this via code.

I tried using the auto-fill feature of excel, but it keeps trying to increment the "C2" portion of my address rather than the ARC### portion.

It's easy to use auto-fill to create a column of all the worksheet names (see my image below, column A) so hopefully that can be of help.

Thank you for your help!

In A1 and autofilll down: ARC001

In B2 and copy down =INDIRECT("'" & A1 & "'!C2")

Cheers! That did the trick!

Thank you so much for your help. That function was just what I was looking for.

You're welcome