vlookup separate workbooks and sum the results

[Sotos]

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

[Rob342]

Sotos
Post a copy of your wb and required results
Rob

[Sotos]

hi Rob342

so in the first wb i have

Num
Blank
Char
Find


522

a



523

b



524

c



525

d



526

e



527

f



528

g



529

h



530

i



531

j



532

k



533

l



534

m



535

n



536

o



537

p



538

q



539

r



540

s



541

t



542

u



543

v




in the second wb i have this


Char
col1
col2
col3
col4
col5
col6
col7
col8
col9


a
5,00
6,00
7,00
8,00
9,00
10,00
11,00
12,00
13,00


b
0,00
1,00
2,00
3,00
4,00
5,00
0,00
0,00
0,00


c
0,00
1,00
2,00
3,00
4,00
5,00
6,00
7,00
8,00


d
3,33
2,33
8,00
0,33
0,67
1,67
0,67
1,33
2,00


e
5,83
4,83
3,00
2,83
1,83
0,83
1,83
1,17
0,50


f
8,33
7,33
3,00
5,33
4,33
3,33
4,33
3,67
3,00


g
10,83
9,83
0,33
7,83
6,83
5,83
6,83
6,17
5,50


h
13,33
12,33
2,83
10,33
9,33
8,33
9,33
8,67
8,00


i
15,83
14,83
5,33
12,83
11,83
10,83
11,83
11,17
10,50


j
18,33
17,33
7,83
15,33
14,33
13,33
14,33
13,67
13,00


k
20,83
19,83
10,33
17,83
16,83
15,83
16,83
16,17
15,50


l
23,33
22,33
12,83
20,33
19,33
18,33
19,33
18,67
18,00


m
25,83
24,83
15,33
22,83
21,83
20,83
21,83
21,17
20,50


n
28,33
27,33
17,83
25,33
24,33
23,33
24,33
23,67
23,00


o
30,83
29,83
20,33
27,83
26,83
25,83
26,83
26,17
25,50


p
33,33
32,33
22,83
30,33
29,33
28,33
29,33
28,67
28,00


q
35,83
34,83
25,33
32,83
31,83
30,83
31,83
31,17
30,50


r
38,33
37,33
27,83
35,33
34,33
33,33
34,33
33,67
33,00


s
40,83
39,83
30,33
37,83
36,83
35,83
36,83
36,17
35,50


t
43,33
42,33
32,83
40,33
39,33
38,33
39,33
38,67
38,00


u
45,83
44,83
35,33
42,83
41,83
40,83
41,83
41,17
40,50


v
48,33
47,33
37,83
45,33
44,33
43,33
44,33
43,67
43,00



and the third i have this



Num
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11


522
100
0
0
0
1
10
33
0
0
22
30


523
100
15
0
0
2
11
34
42
0
23
31


524
100
15
22
0
3
12
35
43
0
24
32


525
100
15
0
1
4
13
0
44
0
25
33


526
22
15
24
2
5
14
37
45
0
26
34


527
22
54
25
3
6
15
38
46
0
27
35


528
22
0
26
4
7
0
0
47
13
28
36


529
22
54
27
5
8
17
40
48
14
29
37


530
22
54
0
6
9
18
41
0
0
30
38


531
22
54
29
7
10
19
42
50
16
31
39


532
22
54
30
8
11
20
43
51
17
32
40


533
22
54
0
9
12
0
0
52
18
33
41


534
22
54
32
10
13
22
45
53
19
34
42


535
22
54
33
11
14
23
46
54
0
35
43


536
22
54
34
12
15
24
47
55
21
36
44


537
22
0
35
13
16
25
48
56
0
37
45


538
22
54
36
14
17
26
49
57
23
38
46


539
22
54
37
15
18
0
50
58
24
39
47


540
22
0
38
16
19
0
51
59
0
40
48


541
22
54
39
0
20
0
52
60
26
41
49


542
22
54
40
18
21
0
53
61
27
42
50


543
22
54
41
19
22
0
54
62
28
43
51

[Sotos]

i want in the first wb to bring me back, under the "Find" (for example D2) for the number "522" the col1 from the third wb and for the "a" from the second wb col2 and then multiply those numbers. so the result would be 100*6 =600. do that for the entire row (for 522 col 2 and for a col4 the result would be 0+8 =8)and then add all those numbers and bring back the result in D2. The same for D3 and so on....
Does it make any sense?

[Rob342]

Clear as MUD !
Still need to see workbook 1 and can you create the data on sheet1,2,3 as i do not to replicate all the figures
i am presuming that all 3 workbooks are open at the same time?
If we copy the data from wb2 & wb3 into this workbook and then delete it afterwards, would that create a problem

[Sotos]

Clear as MUD !
Still need to see workbook 1 and can you create the data on sheet1,2,3 as i do not to replicate all the figures
i am presuming that all 3 workbooks are open at the same time?
If we copy the data from wb2 & wb3 into this workbook and then delete it afterwards, would that create a problem

for some reason i can't attach the workbooks. just copy paste the data i sent you in three different workbooks starting from A1. thats how i have it.

also i would like for them to be closed and ask from the code to retrieve the data from the closed workbooks

[Rob342]

Sotos
have copied the data over however
am i presuming the char column in wb2 is for ref only and the num column on wb3 is ref only ???????
and you still have not told me what the colums are on wb1 sheet 1 do they start at A,B,C,D,E ETC
Rob

[Sotos]

Sotos
have copied the data over however
am i presuming the char column in wb2 is for ref only and the num column on wb3 is ref only ???????
and you still have not told me what the colums are on wb1 sheet 1 do they start at A,B,C,D,E ETC
Rob

Yes, dont mind about the col1 col2 etc...it's just the a header i put. Everything you copy paste it straight to A1 for each workbook.
like all tables we have a name for each column. thats all. so num char find col1.....all these are in the first row...

[Rob342]

Sotos
I would rethink your option to use VLookUp's as they are a nightmare, if you don't trap them for all the errors
you will need to do this yourself.
As i haven't got the full Jist of all the lookups your trying to achieve, i have done you a sample code attached
I will leave it to you to complete the rest of it as i am on holiday from Sat to early July
Please read the comments within the code, that will give you an idea of what's what ok
Rob