Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help me for exact Formula
I have data on sheet1 & sheet2 and I want to apply formula on sheet3 to link
the Qty. while I have try with Vlookup and Sumif but got success only as Vlookup(sheet1€¦..)+Vlookup(sheet2€¦..) Sheet1 Code Imported Material Qty 101 A 5 300 B 10 400 C 7 Sheet2 Code Local Material Qty 102 X 3 104 Y 8 203 Z 2 Sheet3 Code Material used Qty 101 A 5 102 X 3 104 Y 8 203 Z 2 300 B 10 400 C 7 Thanks Zafar |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help me for exact Formula
Hi
Assuming that your codes will always exits in one sheet or the other. First create 2 named ranges InsertNameDefine Name tbl1 Refers to =Sheet1$A$1:INDEX(Sheet1$C:$C,COUNTA(Sheet1!$A:$A) ) Name tbl2 Refers to =Sheet2$A$1:INDEX(Sheet2$C:$C,COUNTA(Sheet2!$A:$A) ) On Sheet3 in cell C2 =IF(COUNTIF(Sheet1!A:A,Sheet3!A2), VLOOKUP(Sheet3!A2,tbl1,3,0),VLOOKUP(Sheet3!A2,tbl2 ,3,0)) Copy down as far as required -- Regards Roger Govier "vlook fomula" wrote in message ... I have data on sheet1 & sheet2 and I want to apply formula on sheet3 to link the Qty. while I have try with Vlookup and Sumif but got success only as Vlookup(sheet1€¦..)+Vlookup(sheet2€¦..) Sheet1 Code Imported Material Qty 101 A 5 300 B 10 400 C 7 Sheet2 Code Local Material Qty 102 X 3 104 Y 8 203 Z 2 Sheet3 Code Material used Qty 101 A 5 102 X 3 104 Y 8 203 Z 2 300 B 10 400 C 7 Thanks Zafar |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help me for exact Formula
Hi,
Thanks problem is resolved only in this condition if i have data on two sheets and need result on sheet3, but now i have data more than two sheets and i need link on a final sheet and in this condition i could not found result "Roger Govier" wrote: Hi Assuming that your codes will always exits in one sheet or the other. First create 2 named ranges InsertNameDefine Name tbl1 Refers to =Sheet1$A$1:INDEX(Sheet1$C:$C,COUNTA(Sheet1!$A:$A) ) Name tbl2 Refers to =Sheet2$A$1:INDEX(Sheet2$C:$C,COUNTA(Sheet2!$A:$A) ) On Sheet3 in cell C2 =IF(COUNTIF(Sheet1!A:A,Sheet3!A2), VLOOKUP(Sheet3!A2,tbl1,3,0),VLOOKUP(Sheet3!A2,tbl2 ,3,0)) Copy down as far as required -- Regards Roger Govier "vlook fomula" wrote in message ... I have data on sheet1 & sheet2 and I want to apply formula on sheet3 to link the Qty. while I have try with Vlookup and Sumif but got success only as Vlookup(sheet1€¦..)+Vlookup(sheet2€¦..) Sheet1 Code Imported Material Qty 101 A 5 300 B 10 400 C 7 Sheet2 Code Local Material Qty 102 X 3 104 Y 8 203 Z 2 Sheet3 Code Material used Qty 101 A 5 102 X 3 104 Y 8 203 Z 2 300 B 10 400 C 7 Thanks Zafar |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help me for exact Formula
Hi
Then I think you would need to do the following. Title your summary sheet as Summary It might be easier to maintain, and avoid the possibility of the formula growing too long for any one cell, if you placed these lookups in separate cells to the right of where you want your answer - for example in cell N1 of sheet Summary enter ="Sheet"&COLUMN(A1)&"!A:C" Copy across through cells O1, P1, Q1 for as many sheets as you require to lookup. in cell N2 enter =IF(COUNTIF(INDIRECT(N$1),$A2),VLOOKUP($A2,INDIREC T(N$1),3,0),0) Copy across for as many columns as required, then down for as many rows as required Then in cell C2 of Summary, enter =SUM(N2:Z2) and copy down as far as required -- Regards Roger Govier "vlook fomula" wrote in message ... Hi, Thanks problem is resolved only in this condition if i have data on two sheets and need result on sheet3, but now i have data more than two sheets and i need link on a final sheet and in this condition i could not found result "Roger Govier" wrote: Hi Assuming that your codes will always exits in one sheet or the other. First create 2 named ranges InsertNameDefine Name tbl1 Refers to =Sheet1$A$1:INDEX(Sheet1$C:$C,COUNTA(Sheet1!$A:$A) ) Name tbl2 Refers to =Sheet2$A$1:INDEX(Sheet2$C:$C,COUNTA(Sheet2!$A:$A) ) On Sheet3 in cell C2 =IF(COUNTIF(Sheet1!A:A,Sheet3!A2), VLOOKUP(Sheet3!A2,tbl1,3,0),VLOOKUP(Sheet3!A2,tbl2 ,3,0)) Copy down as far as required -- Regards Roger Govier "vlook fomula" wrote in message ... I have data on sheet1 & sheet2 and I want to apply formula on sheet3 to link the Qty. while I have try with Vlookup and Sumif but got success only as Vlookup(sheet1€¦..)+Vlookup(sheet2€¦..) Sheet1 Code Imported Material Qty 101 A 5 300 B 10 400 C 7 Sheet2 Code Local Material Qty 102 X 3 104 Y 8 203 Z 2 Sheet3 Code Material used Qty 101 A 5 102 X 3 104 Y 8 203 Z 2 300 B 10 400 C 7 Thanks Zafar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 | Excel Discussion (Misc queries) | |||
Exact formula 3 Columns | Excel Worksheet Functions | |||
EXACT Formula needed? | Excel Discussion (Misc queries) | |||
How to do the exact copy of a formula | Excel Worksheet Functions | |||
EXACT formula | Excel Discussion (Misc queries) |