From: maggiemay on 16 Feb 2010 22:37 I prepared a worksheet with years and names. This worksheet showed names, dates and other data (total five columns) with the names (column 2) in alphabetical order A to Z. I copied this to a second worksheet which showed all the same data but in year order (column 1) 1999 to 2010. Can I now insert a row of data in the first worksheet and get it to automatically be inserted in the correct position in the second worksheet? From: Kate on 17 Feb 2010 05:55 "maggiemay" wrote: > I prepared a worksheet with years and names. This worksheet showed names, > dates and other data (total five columns) with the names (column 2) in > alphabetical order A to Z. > I copied this to a second worksheet which showed all the same data but in > year order (column 1) 1999 to 2010. > Can I now insert a row of data in the first worksheet and get it to > automatically be inserted in the correct position in the second worksheet? Hi Maggiemay, Have you found any information yet regarding your post? I need to know the exact same thing! Thanks :-) From: JLatham on 17 Feb 2010 10:23 IF the names in the lists are unique (i.e., Tom Smith doesn't show up 2 or 3 or more times), then you can use a VLOOKUP() formula on the second sheet to find data related to that name. You said that the names in the Original sheet were in column 2 (I'm assuming column 2 is column B). And they're also in column B on the Sorted sheet, just in a different sequence because of sorting on the year. On the original sheet, which we will call OriginalSheet, your names are in column B and your information (including the new column) goes over to column F, and the names are in rows 1 through 55. On the second sheet, in any column where you want to return information from that first sheet, you would use a VLOOKUP formula. This formula would be for the name in row 2 (cell B2) of that sheet: =VLOOKUP(OriginalSheet!\$B\$1:\$F\$55,\$B2,2,False) That would return the value from column C of the Original Sheet. The key here is that ,2, entry in the formula. That number determines which column of the referenced table (OriginalSheet!\$B\$1:\$F\$55) holds the value you want to be returned. For that table, values from 1 to 5 would be valid, with 1 being the name itself (1st column) and 2 through 5 being other values from columns C, D, E or F of that table. That formula will fill nicely down through the entire list on your copy sheet. I hope this helps both you and Kate. "maggiemay" wrote: > I prepared a worksheet with years and names. This worksheet showed names, > dates and other data (total five columns) with the names (column 2) in > alphabetical order A to Z. > I copied this to a second worksheet which showed all the same data but in > year order (column 1) 1999 to 2010. > Can I now insert a row of data in the first worksheet and get it to > automatically be inserted in the correct position in the second worksheet?  |  Pages: 1 Prev: if statements for multiple entriesNext: MS Word equation