Referencing a cell in another worksheet using a variable

[jjmax]

Simple explanation -

I want to reference a cell in a work sheet. Easy enough I just type =Sheet1!A1.
The problem I have is that I will need to run a report every two weeks so the cell's column will change. For example in week 1 the column would be A so the above formula is fine. But in week2 the column would be B, etc...
I figured the easiest thing to do would be to ask the user what column they were working on in that week, then update the formulas with that information.
For example a user would type A in week one and then the formula would be =Sheet1!A1, in week 3 the user would type C and then that would make the formula = Sheet1!C1
The problem is I have no idea how to do this and all my attempts have failed.Is there an easy way to do this in Excel?

Longwinded Explanation -
The workbook is a student gradebook I keep in school for grades 1-12. The boss at my school has asked that we submit this every two weeks, but we need to use their excel file.
I've made a copy of their file and added it as a sheet in my workbook.
I would now like to populate this with data from two other sheets in the workbook.
I've added another sheet that just asks for the letter value for the column of the week the report is being run for. The reason I did it this way is that I want to give this some colleagues. So Id like to keep it as user friendly as possible.
I'm not even sure if I'm going about this in the right way. Is there a better solution? I don't know VB and have only limited experience with Excel formulas.

Thanks for the help.

John

 

[VoG]

With C in A1 this returns Sheet1!C1

=INDIRECT("Sheet1!"&A1&1)

 

[jjmax]

Hi VOG

Thanks for the reply.

I get an error when I use this - #REF! is all that appears in the cell.

I appreciate the help,

John

 

[VoG]

That would happen if Sheet1 did not exist. Example

Excel Workbook
	C
1	A test
Sheet1

Excel Workbook
	A	B
1	C	A test
Sheet2

 

[jjmax]

It works! Brilliant! Thank you very much.

This is the formula I am using
=INDIRECT("Sheet2!"& Sheet1!A1 & 2)

Sheet2 contains the data I need to copy into the weekly report sheet.
Sheet1 contains the cell with the column letter as input by the user.

This has been a huge help so thank you again for taking the time to reply.

Is this the best way to approach this problem, or is there a better way?
I'm happy with this approach, it'll take a lot of pointless repetitive work out of my life!