Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorted Linked data doesnt stay together
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. Heres 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorted Linked data doesnt stay together
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." wrote: 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. Heres 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorted Linked data doesnt stay together
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" wrote: 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." wrote: 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. Heres 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorted Linked data doesn't stay together
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 --- "Alice M." wrote in message ... 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. .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scroll wheel doesn´t work in excel 2007 | Excel Discussion (Misc queries) | |||
Scroll wheel doesn´t work in excel 2007 | Excel Discussion (Misc queries) | |||
Displaying linked data in excel 2007 when linked file is not avail | Excel Discussion (Misc queries) | |||
Question about deleting rows from sorted linked worksheets | Excel Worksheet Functions | |||
Excel-rolling data linked to chart & worksheet columns must stay | Charts and Charting in Excel |