Hi,

I'm fairly comfortable with excel but I got a head-scratcher here that no one in our office could figure out.

Scenario:

In column A, I have a list of students, In column B is whether they read a particular book, in C is another book and so on.

I've created a masterlist sheet and a template sheet but now I want to use my template sheet and copy paste it into multiple sheets but I need my formulas to change according to the sheet it's on.

EG: template formula =IF(Masterlist!B1="yes","Yes","No")
needs to change to =IF(Masterlist!C1="yes","Yes","No") if it's on a 4th sheet.

What would I need to do to change the row, to change the column, and change both? (I am just trying to figure what book are most popular and what the student actually did this term)

Lastly, is there a method of automating the creation of sheets by using the template?


EDIT: I am using excel 2007