Problem with pasting excel rows/data as "link"

[Michelle York]

I need help fixing a problem.

I have an Excel workbook with multiple worksheets--one for each teacher in
my school. I have some data on one worksheet that I want to paste as a link
to another worksheet so that it will automatically update when the
information changes in the first. It's because I have SPED students listed
in 2 places--their classroom teacher's worksheet and their SPED teacher's
worksheet. I want to only have to enter testing data once--on the classroom
teacher's page--then have it automatically update on the SPED teacher's
page.

I've copied the cells of the appropriate students from the classroom page,
then gone to the SPED teacher's page and "pasted special", "all", "paste
link". It appears to work perfect. If I change data on the classroom page,
it updates the SPED page.

HOWEVER, if I move a student's data into a new row on the classroom page (if
adding/deleting other students, the row # changes), it updates the SPED page
to show the student who now has the OLD row #. Therefore, it's updating
based on the row number instead of updating based on the student's name. If
I move the student to a new row, the link doesn't follow it--it remains in
the original row.

After writing all of this, I'm not even sure if it makes any sense.
Hopefully, it will. If anyone has any ideas of how to get it to do what I
want, please let me know.

The only other problem I've noticed when "pasting link" is that cells that
WERE blank on the original page, now show up on the linked page with a "0"
and a yellow diamond that says "formula refers to empty cell". That's not a
HUGE deal, because I know that as I enter data into those empty cells, this
should go away, but it's still annoying to see all of those zeros in cells
that should have stayed empty.

Running Excel (Office 2004) on a Mac Powerbook 10.3.9.

Michelle York
Literacy Coach (1-5)
Highland Park Central Elementary School
Topeka, Kansas

 

[Ken Johnson]

Hi Michelle,
I think I know what you are trying to do.
Instead of trying to paste a link I used VLOOKUP.
Say, Sheet1 is the Normal Class and Sheet2 is the SPED students from
the Normal Class.
Say, there are 25 students on Sheet1 and 10 SPED students on Sheet2.
Say, the names start at cell A3 on both sheets.
Say, you plan on never having more than 30 students in any class and
that the maximum number of tests is never going to exceed 10.

I'm changing computer now, my son wants this one.

Ken Johnson

 

[Ken Johnson]

Hi Michelle,
Type the following worksheetfunction into cell B3 on Sheet2:

=VLOOKUP($A3,Sheet1!$A$3:$K$32,COLUMN(),FALSE)

Then Fill across to the last test column on Sheet 2 and down to the
last row with a SPED student name. Note that because of the absolute
references ($'s), the only address that changes, as you do the fill, is
the row number in the first argument ($A3) that tells Excel which
student to lookup. The COLUMN() argument ensures that the test mark
that ends up in say column 5 has come from the test mark in column 5 of
Sheet1.
Note also that if you have changed the sheet names from say, Sheet 1
to Class 1 then when you type in the above worksheetfunction change
Sheet1! to 'Class 1'! .
(Note the single quotes)

If you do it this way you must make sure you don't ever drag any SPED
student names, on the SPED sheet, to different cells. The formula
updates and the correct marks for that student don't move to the moved
student's new row. I've tested it to see what happens when you sort the
SPED students and it works fine i.e, names and marks move together.
When I did the sort I selected all the SPED student names and all the
test marks (Not the test headings) and selected "My list has no header
row".

Concerning those ugly "0"'s you get when you paste a link from a blank
source cell you can do this:

1) Select the topmost/leftmost cell with the pasted link.
2) Look in the formula bar. Say it has =Sheet1!A1, change it to
=IF(Sheet1!A1<>"",Sheet1!A1,"")
3) Fill the new formula across and down to the rightmost/bottommost
cell to show all the pasted links. Pasted blanks will then be just
that, not 0's.

I hope this all makes sense. I'm not an Excel expert, just a high
school science teacher teaching adolescents in Sydney, Australia. I use
Excel often and for many different reasons.

Ken Johnson

 

[Ken Johnson]

Hi Michelle,
I forgot to mention that I tested it out to see what happens when you
delete a student from the Main Class sheet and it works perfectly. If
you have to delete a SPED student you can delete them from the Main
Class sheet first, then when you go to the SPED sheet you can clearly
see which student to delete since Excel could no longer find that
student and replaces all their marks with #N/A's.

By the way, what exactly is a SPED student? Have they been booked for
learning too quickly ;-) or is it short for something like Special Ed?
Ken Johnson