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