Transferring data between worksheets

R

roy.okinawa

I need a formula that will allow me to do this:

Worksheet 2 is the master file and contains various information. Worksheet
1 is the input sheet. If Column A sheet 1 matches Column A sheet 2, copy
data from Column B sheet 2 and input into Column B sheet 1.

Is this possible? Thanks.
 
R

roy.okinawa

I getting a #REF in cell B on sheet 1. ???

Since both Sheet 1/2, cell A match, the number from sheet 2, cell B should
have copied over to sheet 1, cell B. Correct?
 
C

Chip Pearson

Since both Sheet 1/2, cell A match, the number from sheet 2,
cell B should
have copied over to sheet 1, cell B. Correct?
Click to expand...

Correct. The formula works for me. Are you sure you are using
the correct sheet names?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




message
 
R

roy.okinawa

OK. It is working now, however before it copies itself, a Update Values
screen pops up asking to choose a excel file. Which would be the same file I
am working from. Should that happen?

Thanks
 
C

Chip Pearson

You only get that dialog when the sheet named in the formula
doesn't exist, so Excel asks you where to find it. What are the
sheet names (on the tabs) of the sheets you're using?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
 
R

roy.okinawa

Thank you very much.

The formula is now working. I just needed to update my tabs.
 
R

roy.okinawa

Again, thank you for the help.

One more question: When I type a number in cell A of sheet 1, column A, is
it possible for all of column A, sheet 2 be checked for like number and do
the transfer of data just like below.
 
M

Max

roy.okinawa said:
... When I type a number in cell A of sheet 1, column A, is
it possible for all of column A, sheet 2 be checked
for like number and do the transfer of data ..
Click to expand...

One way ..

Assuming that data starts in row2 down

In Sheet1
---------
Put in B2:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"",
INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)))

Copy B2 down as far as required
 
R

roy.okinawa

Below is an example of what it does now. When I type a number in column A on
the Input sheet it looks for a match on the Master sheet, column A and copies
the data from column B to column B on the Input sheet. However, when I type
the same number further down the column I want it to scan the Master file
column for a matching number and copy. I don't want to have the same number
listed more than once on the master sheet. Hope that clears it up.

Input sheet
Column A Column B
292001 17

Master sheet
Column A
292001 17
 
R

roy.okinawa

Forget what I just sent. It works now. Thank you so very much. I must have
type something wrong.
 
Top