I hate merged cells, but... you need 4 separate formulas on Sheet2, B1:B4.

In B1
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0)-1,1)
in B2
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0),1)
in B3
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0)+1,1 )
in B4
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0)+2,1 )

Those will work, but if there's no ID in the merged cell on Sheet2, they
will all show #N/A. To get around that, we wrap the formulas in a trap like
these:
in B1
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$ A,0)-1,1)),"",OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$ A,0)-1,1))
in B2
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$ A,0),1)),"",OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$ A:$A,0),1))
in B3
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$ A,0)+1,1)),"",OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1 !$A:$A,0)+1,1))
and in B4
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$ A,0)+2,1)),"",OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1 !$A:$A,0)+2,1))

To explain a little about the basic formula:
OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0)-1,1)
The $A$1 following MATCH( refers to the cell on Sheet2 that has the ID
typed into it that you need matching information from on Sheet1.
The ,Sheet1!$A:$A, in the middle of the MATCH() formula refers to the
column on Sheet1 where your IDs are at. I presume that there is more than
just one entry on that sheet - but the formulas I gave will work for 1 or
1000. Although if there is just one ID/name/address/city/phone entry on the
first sheet, we can certainly simplify things a LOT1


"Robin" wrote:

I have Sheet1 and Sheet2. In Sheet1, Column A, I merged rows 1-4 as one cell
to contain the ID # for the person Column B: name in Column B1, address in
B2, city in Column B3, and phone # in Column B4. When I entered the ID# in
Column A of merged rows 1-4 in Sheet2, I need the information from Column
B1-B4 of Sheet1 to transfer to Sheet2, Column B1-B4 automatically. I would
appreciated if someone can give me a hand....thanks.