Sorted Linked data doesn’t stay together

[Alice M.]

Example: My workbook has two sheets:

Sheet One: Contains typed employee names in Col A. Col B is a pasted link
from a column in Sheet Two.

Sheet Two: Col A is a mirror of Col A in Sheet One. It contains pasted links
from the names in Sheet One.

In Sheet Two, Cols B-M (labeled Jan-Dec) contain hours worked in each month,
followed by Col N which is the sum of Col B-M.

The sum in Col N in Sheet Two links to Col B of Sheet One.

Here’s the problem:

When I add a name to the list in Sheet One, then sort, the name list in
Sheet Two sorts OK, but the hours do not stay with the original name.

 

[Max]

Try it like this:
Link it the other way around (make Sheet2 as the master?), then use an
index/match to retrieve col N in Sheet1

1. Freeze the names in Sheet2's col A
2. In Sheet1, link it to Sheet2's names, eg
in say A2, copy down:
=IF(Sheet2!A2="","",Sheet2!A2)

Then place this in B2, copy down:
=IF(A2="","",INDEX(Sheet2!N:N,MATCH(A2,Sheet2!A:A,0)))
to return the total hours from Sheet2's col N

Now you can sort the names in Sheet1, and the results from Sheet2's col N
will adjust accordingly to follow. New names are added to Sheet2 (as the
master)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[Alice M.]

Thank you Max, your solution works perfectly for the example I gave in my
post, which involved only two sheets.

But I gave a hypothetical problem, one much simpler than my real situation.
I did that because I was having difficulty trying to explain it. I'm hoping
some variation of your solution is adaptable.

Here is the real problem: My workbook has 13 sheets:

Sheet#1 is a primary sheet with typed employee names in Cols A and B
(L/Name, F/Name), followed by a column for every month of the year, ending
with a column to sum the YTD hours from Jan-Dec. The hours that appear in
each column are linked from Sheets 2-13.

Sheets#2-13 are identical, but each is tabbed for a month of the year. Cols
A-B of each of these sheets has a pasted link from Sheet#1 so as to repeat
the employee names.

The remaining columns are for recording hours. Each column is labeled for
various tasks. The last column sums the hours for each employee. It happens
to be Col P.

The sum in Col P is link-pasted to Sheet#1 (Col P in the Jan sheet, for
example, appears in the Jan column in Sheet#1 and so on.)

So now, the problem remains the same about adding or deleting names (rows)
in Sheet#1, and having the linked hourly data stay with the original name.

Is there a way to reverse the links so that when names are added or deleted
or sorted in Sheets 2-13, the new info is linked to Sheet#1?

(It seems that if I did not keep Sheet#1 as THE primary sheet, capable of
changing all other sheets when a name is added, deleted, or sorted, it would
be a problem.)

Thank you. Sorry for the lengthy question.

 

[Max]

I'm out of ideas for you. Hang around awhile. Maybe others would step in
with their views.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik