linking two sheets in a third sheet

Hey,

I have a problem and want to solve it in excel.
I have two sheets as follows:

Sheet1 : Sheet2:
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 2 yy yyy
6 Z 3 zz zz
4 ee ee
5 ss ss
6 dd dd

I want to make a third sheet with all the information of sheet 1 and only the informatie of sheet 2 for the lines with an identical number in sheet1 (linking sheet1 and sheet2 via Nr)

Results of sheet 3 :
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 4 ee ee
6 Z 6 dd dd

So, for all the lines in sheet1, I searched the linked information in sheet2 and shows alle the information per number in sheet3

Anyone an idea how to do this. Maybe I have to write VBA, but I never did that.
An other way is to read all the informatie in Access (but there are to much sheets to link and I want to know if there is now way in excel or in vba)

thanks in advance
Nic

Hi
try the following
- copy your data from sheet1 to your third sheet
- in column C (cell C1) of sheet3 enter
=VLOOKUP(A1,'sheet2'!$A$1:$D$100,2,0)

and copy down

in D1 enter
=VLOOKUP(A1,'sheet2'!$A$1:$D$100,3,0)


--
Regards
Frank Kabel
Frankfurt, Germany


Nic wrote:
Hey,

I have a problem and want to solve it in excel.
I have two sheets as follows:

Sheet1 : Sheet2:
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 2 yy yyy
6 Z 3 zz zz
4 ee ee
5 ss ss
6 dd dd

I want to make a third sheet with all the information of sheet 1 and
only the informatie of sheet 2 for the lines with an identical number
in sheet1 (linking sheet1 and sheet2 via Nr)

Results of sheet 3 :
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 4 ee ee
6 Z 6 dd dd

So, for all the lines in sheet1, I searched the linked information in
sheet2 and shows alle the information per number in sheet3

Anyone an idea how to do this. Maybe I have to write VBA, but I never
did that.
An other way is to read all the informatie in Access (but there are
to much sheets to link and I want to know if there is now way in
excel or in vba)

thanks in advance
Nic

One way ..

In Sheet3
------------
Put in A1: =OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy across to B1, then down as many rows as there is data in Sheet1

Copy the labels in C1, D1, etc from Sheet2 paste in Sheet3's corresponding
cells

Put in C2:
=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)),"",OFFSET(Shee t2!$A$1,MATCH($A2,Sheet2!$
A:$A,0)-1,COLUMN(A1)))

Copy C2 across to D2*, then down as many rows as there is data in Sheet1
*or across as many cols as necessary to extract from Sheet2

For a neater look, we can suppress extraneous zeros from showing via:
Tools Options View tab Uncheck "Zero values" OK

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"Nic" wrote in message
...
Hey,

I have a problem and want to solve it in excel.
I have two sheets as follows:

Sheet1 : Sheet2:
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 2 yy yyy
6 Z 3 zz zz
4 ee ee
5 ss ss
6 dd dd

I want to make a third sheet with all the information of sheet 1 and only
the informatie of sheet 2 for the lines with an identical number in sheet1
(linking sheet1 and sheet2 via Nr)

Results of sheet 3 :
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 4 ee ee
6 Z 6 dd dd

So, for all the lines in sheet1, I searched the linked information in
sheet2 and shows alle the information per number in sheet3

Anyone an idea how to do this. Maybe I have to write VBA, but I never did
that.
An other way is to read all the informatie in Access (but there are to
much sheets to link and I want to know if there is now way in excel or in
vba)

thanks in advance
Nic