how to automatically update a second worksheet when data inserted

[maggiemay]

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?

 

[Kate]

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

 

[JLatham]

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.