Filling Formulas

[TX Teacher]

I maintain a gradesheet for each student. I have a summary sheet displaying
the students' names and points per category. I understand absolute and
relative cell references. I generate the formulas for the first student
accessing his/her sheet and the various cells. Then I fill the formulas down
for all other students. The problem: Can a reference to a sheet be relative?
In other words, if my formulas in Row 3 pull from Sheet 4, and I fill those
formula down and have the formulas in Row 4 pull from Sheet 5? I currently
copy all the formulas and then change the sheet reference manually. Since
each student has 5 formulas and I have 20 students, that's a lot of work.

 

[Mike H]

If your student names in the worksheet are the same as you sheet names then
try this:

Student names in column A and this in column B
=INDIRECT(A1&"!A1")
Dragged down it will retrieve A1 from each sheet if the worksheet is the
same as the name in A1

Mike

 

[TX Teacher]

I keyed in just what you wrote. However, I get the #Ref! error. I played
around with it and research the "indirect" function. I can't figure out what
is wrong. My students names are listed in Column A on my summary sheet. Each
student's individual sheet has been renamed with their name. I check the
spelling, etc. What I want is for, lets say, B8 on the summary to show the
results of a formula in C29 on a sheet named John Doe. When I copy that down,
I want the C29 to remain absolute, and I want the Joe Doe to change to the
next sheet name. Help????

Pat