Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do i transfer cell data from one sheet to the other in Excel
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do i transfer cell data from one sheet to the other in Excel
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do i transfer cell data from one sheet to the other in Excel
another thought ..
In Sheet2, With IDs input in A1, A5, A9, etc Put in B1: =IF(INDIRECT("A"&(INT((ROW(A1)-1)/4)*4+1))="","",OFFSET(INDIRECT("'Sheet1'!A"&MATCH( INDIRECT("A"&(INT((ROW(A1)-1)/4)*4+1)),Sheet1!A:A,0)),MOD(ROW(A1)-1,4),1)) Copy B1 down as far as required to cover the expected IDs in col A. B1:B4 returns results for ID input in A1, B5:B8 returns results for ID input in A5, and so on. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do i transfer cell data from one sheet to the other in Excel
If you're only looking up one ID number in A1:
Enter this formula in B1 and copy down to B4: =INDEX(Sheet1!B:B,MATCH(A$1,Sheet1!A:A,0)+MOD(ROW( A4),4)) If you're lookup multiple ID numbers that are spaced every 4 rows: A1, A5, A9 etc, then enter this formula in B1 and copy down as needed: =INDEX(Sheet1!B:B,MATCH(OFFSET(A1,-MOD(ROW(A4),4),,),Sheet1!A:A,0)+IF(MOD(ROW(A1),4), MOD(ROW(A1)-1,4),3)) Biff "Robin" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do i transfer cell data from one sheet to the other in Exc
Glad you came up with that one. I was trying to work toward something like
that and just couldn't figure it out tonight. "T. Valko" wrote: If you're only looking up one ID number in A1: Enter this formula in B1 and copy down to B4: =INDEX(Sheet1!B:B,MATCH(A$1,Sheet1!A:A,0)+MOD(ROW( A4),4)) If you're lookup multiple ID numbers that are spaced every 4 rows: A1, A5, A9 etc, then enter this formula in B1 and copy down as needed: =INDEX(Sheet1!B:B,MATCH(OFFSET(A1,-MOD(ROW(A4),4),,),Sheet1!A:A,0)+IF(MOD(ROW(A1),4), MOD(ROW(A1)-1,4),3)) Biff "Robin" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I transfer data from 1 sheet to another? | Excel Worksheet Functions | |||
transfer data from one excel sheet to another | New Users to Excel | |||
How do i get my data to transfer to another work sheet? | Excel Discussion (Misc queries) | |||
Transfer data from one sheet to another | Excel Discussion (Misc queries) | |||
How can I transfer a Lotus 123-9 data spread sheet file to Excel? | Setting up and Configuration of Excel |