Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 210
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I transfer data from 1 sheet to another? Syd Excel Worksheet Functions 2 June 7th 06 05:45 PM
transfer data from one excel sheet to another Exceloficio New Users to Excel 3 January 30th 06 06:40 AM
How do i get my data to transfer to another work sheet? Stacey Excel Discussion (Misc queries) 1 January 14th 06 10:06 AM
Transfer data from one sheet to another Cody Excel Discussion (Misc queries) 1 November 28th 05 03:36 AM
How can I transfer a Lotus 123-9 data spread sheet file to Excel? EDECHO Setting up and Configuration of Excel 2 August 26th 05 11:23 PM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"