Sotos
06-04-2019, 06:01 AM
Hi all
Here is my problem....imagine i have three workbooks. in the last workbook i have a cell where i vlookup a cell from the first workbook and vlookup a cell from the second workbook and show their product*. this is done for several columns. then i sum the rows
So, the cell is D2 all the way down to D500
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
VLOOKUP($C3;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A3;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
VLOOKUP($C4;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A4;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
.
.
In E2 i have the same formula but the only thing that changes is the column index from both Vlookup
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;6;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;3;0).
and
F2 the same with different col.index
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;8;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;4;0).
So the first vlookup column index is changing in a step 2 manner and the second one adding one by one column.
this goes up to Q2
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;30;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;15;0).
At the end in cell R2 i sum C2:Q2, R3 =sum C3:Q3..and so on.
What i want is a vba to calculate the above and just bring back the sum in a D column
Here is my problem....imagine i have three workbooks. in the last workbook i have a cell where i vlookup a cell from the first workbook and vlookup a cell from the second workbook and show their product*. this is done for several columns. then i sum the rows
So, the cell is D2 all the way down to D500
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
VLOOKUP($C3;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A3;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
VLOOKUP($C4;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A4;'[Test2]Sheet1'!$A$3:$O$7927;2;0).
.
.
In E2 i have the same formula but the only thing that changes is the column index from both Vlookup
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;6;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;3;0).
and
F2 the same with different col.index
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;8;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;4;0).
So the first vlookup column index is changing in a step 2 manner and the second one adding one by one column.
this goes up to Q2
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;30;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;15;0).
At the end in cell R2 i sum C2:Q2, R3 =sum C3:Q3..and so on.
What i want is a vba to calculate the above and just bring back the sum in a D column