| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data between worksheets
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
In column B on sheet1, use
=IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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? 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 "roy.okinawa" wrote in message ... 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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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 "Chip Pearson" wrote: 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? 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 "roy.okinawa" wrote in message ... 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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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 "roy.okinawa" wrote in message ... 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 "Chip Pearson" wrote: 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? 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 "roy.okinawa" wrote in message ... 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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
Thank you very much.
The formula is now working. I just needed to update my tabs. "Chip Pearson" wrote: 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 "roy.okinawa" wrote in message ... 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 "Chip Pearson" wrote: 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? 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 "roy.okinawa" wrote in message ... 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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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. "Chip Pearson" wrote: 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 "roy.okinawa" wrote in message ... 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 "Chip Pearson" wrote: 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? 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 "roy.okinawa" wrote in message ... 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? "Chip Pearson" wrote: In column B on sheet1, use =IF(Sheet1!A1=Sheet2!A1,Sheet2!B1,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roy.okinawa" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
"roy.okinawa" wrote:
... 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 .. 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
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 "Max" wrote: "roy.okinawa" wrote: ... 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 .. 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
Forget what I just sent. It works now. Thank you so very much. I must have
type something wrong. "Max" wrote: "roy.okinawa" wrote: ... 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 .. 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring data between worksheets
Glad it worked for you !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... Forget what I just sent. It works now. Thank you so very much. I must have type something wrong. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 'Consolidate' data from multiple worksheets | Excel Discussion (Misc queries) | |||
| Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
| sharing rows of data across multiple worksheets within a workbook | Excel Worksheet Functions | |||
| Putting data from multiple worksheets into one | Excel Discussion (Misc queries) | |||
| Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Linear Mode
