Compiling Summary Sheet from Multiple Worksheets

I have an excel workbook that contains worksheets specifying details of different projects. I would like to compile the most pertinent information on a summary sheet in the beginning. I've created the first column in the summary sheet that references cells in the first project (aka worksheet 2). Can I copy this column and paste it for all the other projects, each references the same cells but in separate worksheets?

For example, the summary sheet looks like this:

ROWS
Project Name:
Address:
City:
State:

COLUMNS
Project 1
Project 2
Project 3

If for project 1 under "Project Name" I have "=Worksheet2!B2", "Address" I have "=Worksheet2!B4", etc how do I copy and paste this column of formulas but change them so in the next column they read "=Worksheet3!B2" and "=Worksheet3!B4", etc?

Thank you!

You can use the INDIRECT function. Put the names of your worksheets in B1, C1, D1 etc., then you can use these in column B:

B2: =INDIRECT("'"&B$1&"'!B2")
B3: =INDIRECT("'"&B$1&"'!B3")
B4: =INDIRECT("'"&B$1&"'!B4")

Then you can copy these across to get the data from the other sheets.

Hope this helps.

Pete

Thanks Pete! Worked like a charm.