Automatically link each new cell in a table to a new worksheet

Hello, I am trying to create a formula which achieves the following:
link the first cell in a table (say, C3) to a cell (A1) in another worksheet (ie. C3='Worksheet 1!'A1) and the second cell (C4) in the table to cell A1 in worksheet 2, third (C5) to worksheet 3 etc.
(e.g. C4 =some sort of relation to C3 which means the modification to the formula each time it is copied down the column is to change the worksheet, though not the cell number, to which the cell is linked) in this way I am hoping to create an automatic link between C3 and Worksheet 1, C4 and worksheet 2, C5 and worksheet 3 etc.

I am creating a schedule each week of job interviews. Presently I am doing this by copying a template worksheet for each new schedule. Once interviews have been held I am entering the number of attendees and number of persons hired on the schedule worksheet. I am doing this for multiple cities. I then need to export this data to a new table which tracks the attendance and hiring patterns nationally.

With thanks.

Originally Posted by dewilk

Like this?
Copy formula down as far as you need

In case there is no new sheet (like Sheet4 or 5) modify this formula to:
=IFERROR(INDIRECT("Sheet"&ROW(A1)&"!A1"),"")
in excel 07/10

or to
=IF(ISERROR(INDIRECT("Sheet"&ROW(A1)&"!A1")),"",INDIRECT("Sheet"&ROW(A1)&"!A1"))
in Excel 03

Radical! Thank you. Exciting to learn a new function!

You are welcome.
Thanks for feedback

Oooh, ok, actually I still don't really know what I'm doing.

The formula you've given achieves my end but I'm having trouble when I try to apply this formula with a new set of references and on a new sheet.

Is it possible you could explain what each term in the formula means? In particular what I need to understand is which commands are making the following into terms of the formula:

1) a term for cell reference (always in A1 style and the same cell across worksheets)
2) a term for the specific worksheet (do I need to name my worksheets with a generic name and follow it with a number or will Excel always recognise the first worksheet as 'sheet 1' second as 'sheet 2' etc.?)

When I copy the formula you have given me down the column it creates the right sort of link I need (i.e. a link between a source cell, which is the same across worksheets, containing one category of data) but I will need to create a new formula for each new column of data (each representing a different category of data with a new source cell, the same across worksheets).

My understanding is that in the formula =INDIRECT("Sheet"&ROW(A2)&"A1") --> A2 is referring to sheet 2 (as I observe this term will change to A3, A4 etc. as I paste down the column and as a result the data will be drawn from a new worksheet- I am confused as to why I can also enter this term as B2, or D2 and observe the same pattern) and "A1" is the specific cell reference. But there's something I'm missing...

Appreciative of any direction. (and aware that I really ought to work my way through a comprehensive tutorial!)
With thanks!

Incidentally, I will be using the IFERROR function as I will need for the formula to refer to worksheets that are yet to be created.