Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filling Formulas
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filling Formulas
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filling Formulas
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 "Mike H" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filling formulas | Excel Discussion (Misc queries) | |||
Filling formulas | Excel Discussion (Misc queries) | |||
Filling down formulas in a query table | Excel Discussion (Misc queries) | |||
filling information from one cell and filling another. | Excel Worksheet Functions | |||
Filling Formulas Across Workbooks | Excel Discussion (Misc queries) |