Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy one cell value from sheet 1 to sheet 2 conditionall
Hi I have a couple of worksheets that are making me crazy, and I will
appreciate any idea you can give me: The first worksheet looks as follows: WKS1: cust name postcode custnumber AB me1 4qq 12345 Abc me4 6nu 12346 abcd me3 8fg 12347 abcde me4 1ff 12348 And another that looks as follows: WKS2 custname postcode custnumber Abc me4 6nu AB me1 4qq abcd me3 8fg abcde me4 1ff As you can see: 1) The values in column custname are the same BUT they are NOT in the same order. 2) The custnumber is empty in WKS2. So what I want to do is: from WKS2 pickup the values from custname (one by one) and compare them to the same column in WKS1, if the value match then vaildate if the value in column postcode from WKS1 (is WKS1 no misstake here) is the same as postcode in WKS2. If so copy the value from the correspondent custnumber from WKS1 to WKS2. That is the must have if possible. The nice to have will be to also copy the cell format from WKS1 to WKS2, as some custnumber values are in red, while others are in black. THANK YOU for any idea you can share. Rob. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy one cell value from sheet 1 to sheet 2 conditionall
Hi,
Here is a formula approach: Assume your data start in cell A1 of Sheet1 and Sheet2 so you want to copy the customer number from sheet1 to sheet2 if the name and post code match: In cell C2, under the title, array enter the following formula: =IF(ISNA(MATCH(A2&B2,Sheet1!A$2:A$5&Sheet1!B$2:B$5 ,0)),"",OFFSET(Sheet1!C$1,MATCH(A2&B2,Sheet1!A$2:A $5&Sheet1!B$2:B$5,0),0)) To enter this as an array press Shift+Ctrl+Enter not Enter. copy the formula down as far as necessary. -- Thanks, Shane Devenshire "RobertoB" wrote: Hi I have a couple of worksheets that are making me crazy, and I will appreciate any idea you can give me: The first worksheet looks as follows: WKS1: cust name postcode custnumber AB me1 4qq 12345 Abc me4 6nu 12346 abcd me3 8fg 12347 abcde me4 1ff 12348 And another that looks as follows: WKS2 custname postcode custnumber Abc me4 6nu AB me1 4qq abcd me3 8fg abcde me4 1ff As you can see: 1) The values in column custname are the same BUT they are NOT in the same order. 2) The custnumber is empty in WKS2. So what I want to do is: from WKS2 pickup the values from custname (one by one) and compare them to the same column in WKS1, if the value match then vaildate if the value in column postcode from WKS1 (is WKS1 no misstake here) is the same as postcode in WKS2. If so copy the value from the correspondent custnumber from WKS1 to WKS2. That is the must have if possible. The nice to have will be to also copy the cell format from WKS1 to WKS2, as some custnumber values are in red, while others are in black. THANK YOU for any idea you can share. Rob. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
copy cell from one sheet of worksheet to another sheet | Excel Discussion (Misc queries) | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) |