Prev: if statements for multiple entries
Next: MS Word equation
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 entries
Next: MS Word equation