linking to other worksheets and updating a file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bodster - 09 Oct 2007 20:54 GMT

This is what I want to do,

Sheet 1 contains a list of surnames in column 1, forenames in column 2 along
with other data in columns 3, 4, .......

Sheet 2 contains a list of suranmes, forenames some of which are the same as
in sheet 1.

I want to add a column in sheet 1 which takes the name of the person from
sheet 1, looks up that name in sheet 2 and chooses data from one of the
columns in sheet 2 and places that data back into the correct row and new
column in sheet 1.

A further complication might be that there are duplicate surnames in the
first sheet so need to look up the forenames in these circumstances.

Looking forward to any help,

Cheers

Bodster

Reply to this Message

Earl Kiosterud - 10 Oct 2007 03:37 GMT

Bodster,

You can use VLOOKUP. It looks down one column, so in sheet 2 you'll need to make an
additional column with the surname and forename concatenated. In this case, it could go in
column A, which you will have inserted, causing all the columns to move over. It could also
go in column C (also inserted). In any case, it must be left of the columns which the
VLOOKUPs in sheet will be retrieving data.

= B2 & " " & C2

Now in Sheet 1, in whatever column you want extracted data from sheet 2 to appear, put

=VLOOKUP(A2 & " " & B2, Sheet2!A2:E21,4,FALSE)

This is for row 2 of sheet 1, and retrieves the associated data from column 4 of sheet 2.
Change as necessary.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

> This is what I want to do,
>
[quoted text clipped - 15 lines]
>
> Bodster

Reply to this Message