Is it two different excel worksheets in the same workbookHi,
I want to execute below like query using oledb (referring two different excel)
UPDATE [Onboarded resources$] a SET End_Date = (SELECT End_Date FROM [Sheet1$] b where b.POP_Notes_ID=a.Notes_ID);
How can a
Is it two different excel worksheets in the same workbook
OR
entirely different workbooks?
If it is same workbook, you can execute the query, however, if it is from different workbooks you cannot.
Hi,
Can anyone help me with the Update query in OLEDB
I have columns named Material ,PF in sheet1 and columns named Material and Center in sheet2. I have to update PF in sheet1 by comparing Material values in two sheets and pulling the Center value for whichever the Material value matches..I have to update PF value in sheet1 with Center Value in sheet2 by comparing Materials.
Do you have an option to skip using OLEDB?
You can achieve the same via VLOOKUP formula in excel itself (without needing to use OLEDB). Paste below formula in Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$B$12,2,FALSE)
A2 : Assuming A1=Material, A2 is the first cell containing data
Sheet2!$A$2:$B$12 : Range of all data in sheet2 (B12 is the last row in sheet 2)
Thank you for the solution.
But I have few more columns beside PF(ColB) in Sheet1 named OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE) and Sheet2 also have OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE). So similarly by comparing Material Value in both sheets, I have to fill these 3 cols of sheet1 with 3 respective cols of sheet2.
And for your information, Column A is Material in both the sheets
What should be the formula for each of this??
Thanks in Advance..
Write this formula is Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$E$12,{2,3,4,5},FALSE)
Thank you @sahil_raina_91 for the solution..
I need one more help
I have PLANT in Col B, REVAL IN LC in Col V, REVAL USD in Col W.
Now I want to calculate REVAL USD for all PLANTs.
For Plants 5100,5120,5400,5420,5619, REVAL USD= REVAL IN LC / 4-----(REVAL IN LC divided by 4)
For all other Plants ,REVAL USD= REVAL IN LC
For your information, I have many plants other than 5100,5120,5400,5420,5619 in the PLANTS (Col B)
What is the VLOOKUP formula for this scenario?
Thanks in Advance