Absolute Cell refernce Not working

[DJ Harrington]

I'm trying to do an absolute cell reference but the reference is on another
"tab" or sheet. Same file just different file. I only want the column to
change but to fill the row down.
Formula using:
=Period1!E$5/Period1!E$4

Period1 is the sheet on the same file

and I want to fill down...not over

so switch columns not row...and fill down! Thanks All.

Its for a grade sheet for my teaching. Thanks

 

[DJ Harrington]

Sorry...Period1 is another sheet on the sane file

 

[DJ Harrington]

sorry Period 1 is another sheet on the same file
Same file just different sheet

 

[Bernard Liengme]

But it IS working! Let's say you enter that formula on Sheet1 in A1. The
reference is to a cell on another worksheet in a cell that is 4 columns to
the right of the cell where the formula is (E is four over from A). Now you
drag the formula down to A2, So we want to refer to a column that is 4 to
the right but that is still column E

Try this. Enter the formula in A1, drag to the right as far as needed.
Select B1 to the end and Copy
Click on A2, use Edit | Paste Special with Transpose selected. Now you have
the formula you want in a column and can delete those in the top row

best wishes

 

[DJ Harrington]

Doesn't work..Heres the link to the file
<href="http://www.quickfilepost.com/download.do?get=1d0737cb55d2aeafa66708e80bfbdbbe">My File</a>
http://www.quickfilepost.com/download.do?get=1d0737cb55d2aeafa66708e80bfbdbbe

P1 Student Summary is the table
Assignment's should fill down not over
Just like HW/CW...

Thanks

 

[Bernard Liengme]

I have the file. Please tell me where to find the formula and what is to be
done (let's be sure we are on the same wave length)
best wishes

 

[DJ Harrington]

P1 Student Summary

I have the file. Please tell me where to find the formula and what is to be
done (let's be sure we are on the same wave length)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[DJ Harrington]

I'm sorry I didn't read the whole thing...

On "Period 1" she I want to put the scores and such.

On P1 Student Summary, I want I want A19 to be filled down to like A30
but on the "period1" sheet..I want A19:A30 (P1 Student Summary) to represent
E2:O2 (Period1). Pretty much the same thing for B19..the codes are there but
they just won't fill down, they will only fill across. Thanks sorry for the
late response!

so on Period1 you have this for E5:I5

12 24 25 12 15

on P1 Student summery I want C19:C23 to represent that..but don't want to
hand put all the information.

so for P1 Student summary it should be for C19:C23

P/Given
12
24
25
12
15


Thanks

 

[Bernard Liengme]

Hello DJ:
I will begin by showing how to get just what you want for the first student.
The problem lies in transferring data that is stored horizontally into a
vertical form.
1) On sheet P1 Student Summary, select A19:A36
2) type the formula =TRANSPOSE(Period1!E2:V2) commit this with
CTRL+SHIFT+ENTER (CSE) as it is an array formula. Since; it has been array
entered, it will display in the formula bar as
{=TRANSPOSE(Period1!E2:V2)}
The result is not very interesting but move along

4) select B19:B19, type =TRANSPOSE(Period1!E3:V3); use CSE to enter; and you
get

HW
HW
HW
CW
CW
T
Q etc
5) select C19:C19, type =TRANSPOSE(Period1!E5:V5); use CSE to enter; and you
get
12
24
25
12
15
0 etc

6) I think you can see how to get the D column

It is going to be sheer murder doing this for 25+ students on several
sheets.

As a chemistry prof, I kept electronic grade date since 1968 (first on a
main frame, then on a PC with Lotus123 and then with Excel.) I also wrote a
university student information system. So please let me be bold enough to
make suggestions.
1) We need to get rid of the horizontal to vertical problem. I think the
Period1 sheet can be 'rotated' so that we can more readily take date to
another sheet.
2) It would seem the summary sheets are needed only to print off reports.
Why not design one that processes just one student. Then with some simple
VBA we can cycle through the list of students, printing one for each.

Let's continue this at a personal level - just remove TRUENORTH. from my
email address
best wishes

 

[DJ Harrington]

Hey thanks Bernard for the help..im looking into rotating it. But I also like
the VBA idea!! You have a link or something for somewhere I can start!

Thanks!

Sorry for the late reply!