Sum of vlookups across all worksheets.

[herman]

Good morning,
Could you help me with the following problem ?
In cell A2 of sheet "TOTALS" I want to add up the results of a vlookup
funtion across multiple worksheets.
Thus A2 = vlookup(A1;sheetB!$C:$H;false)+
vlookup(A1;sheetC!$C:$H;false) + vlookup(A1;sheetD!$C:$H;false)
+....... + vlookup(A1;sheetX!$C:$H;false).
X is variable, meaning that sheets may be added or deleted.
Note that the arguments remain constant except for the sheets where
the values are to be looked up.
I don't think this can be handled with any combination of worksheet
functions. Is that right ?
Can anyone help me out here with the most efficient code ?

Thank you very much in advance.
Herman

 

[Don Guillett]

try this

Sub loopws_vlookup()
Dim arr() As Variant
Dim strName As Variant
Dim ws As Worksheet
myarray = Array("July", "Sheet13")
mysum = 0
For Each strName In myarray
mysum = mysum + _
Application.VLookup([b1], _
Sheets(strName).Range("f9:g11"), 2, 0)
Next strName
MsgBox mysum
End Sub

 

[herman]

Hello Don,
First I must tell you that I have to make a little correction to my
example. The formula in cell A2 of Sheet A should be :

= vlookup(A1;sheetB!$C:$H;3;false) + vlookup(A1;sheetC!$C:$H;3;false)
+ vlookup(A1;sheetD!$C:$H;3;false) +....+
vlookup(A1;sheetX!$C:$H;3;false).

I am a very beginner in the VBA field. So I don't know how to
transform your sub into a function. Because a formula is what I
actually need here, right? The formula is to be dragged to the right
to respond to different values in cells B1, C1........

I'm afraid the problem with your solution is that I will have to add a
new sheetname to myarray for each newly inserted sheet.
Thanks for your help anyway.

try this

Sub loopws_vlookup()
Dim arr() As Variant
Dim strName As Variant
Dim ws As Worksheet
myarray = Array("July", "Sheet13")
mysum = 0
For Each strName In myarray
mysum = mysum + _
Application.VLookup([b1], _
Sheets(strName).Range("f9:g11"), 2, 0)
Next strName
MsgBox mysum
End Sub

Good morning,
Could you help me with the following problem ?
In cell A2 of sheet "TOTALS" I want to add up the results of a vlookup
funtion across multiple worksheets.
Thus A2 = vlookup(A1;sheetB!$C:$H;false)+
vlookup(A1;sheetC!$C:$H;false) + vlookup(A1;sheetD!$C:$H;false)
+....... + vlookup(A1;sheetX!$C:$H;false).
X is variable, meaning that sheets may be added or deleted.
Note that the arguments remain constant except for the sheets where
the values are to be looked up.
I don't think this can be handled with any combination of worksheet
functions. Is that right ?
Can anyone help me out here with the most efficient code ?

Thank you very much in advance.
Herman

 

[BubBob]

Does anyone know how to use vlookup to sum multiple values (numbers)
found in the same sheet? Otherwise my case is very similar to this one.

 

[Dave Peterson]

Since you're adding them up, maybe you don't need to do the vlookup at all:

Instead of some variation of:
=sum(Allofthematchesfor(vlookup(a1,sheet2!$a$1:$b$10,2,false)))
(this won't work--don't waste your time)

You could use =sumproduct:

=SUMPRODUCT((Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10))

 

[Don Guillett]

As to changing to a function, try this:

Function wslu(mv)
Dim arr() As Variant
Dim strName As Variant
Dim ws As Worksheet
myarray = Array("July", "Sheet13")
mysum = 0
For Each strName In myarray
mysum = mysum + _
Application.VLookup(mv, _
Sheets(strName).Range("f9:g11"), 2, 0)
Next strName
'MsgBox mysum
wslu = mysum
End Function
=
As to changing to a list. You could use a macro to populate the array.

Hello Don,
First I must tell you that I have to make a little correction to my
example. The formula in cell A2 of Sheet A should be :

= vlookup(A1;sheetB!$C:$H;3;false) + vlookup(A1;sheetC!$C:$H;3;false)
+ vlookup(A1;sheetD!$C:$H;3;false) +....+
vlookup(A1;sheetX!$C:$H;3;false).

I am a very beginner in the VBA field. So I don't know how to
transform your sub into a function. Because a formula is what I
actually need here, right? The formula is to be dragged to the right
to respond to different values in cells B1, C1........

I'm afraid the problem with your solution is that I will have to add a
new sheetname to myarray for each newly inserted sheet.
Thanks for your help anyway.

"Don Guillett" <[email protected]> wrote in message

try this

Sub loopws_vlookup()
Dim arr() As Variant
Dim strName As Variant
Dim ws As Worksheet
myarray = Array("July", "Sheet13")
mysum = 0
For Each strName In myarray
mysum = mysum + _
Application.VLookup([b1], _
Sheets(strName).Range("f9:g11"), 2, 0)
Next strName
MsgBox mysum
End Sub

Good morning,
Could you help me with the following problem ?
In cell A2 of sheet "TOTALS" I want to add up the results of a vlookup
funtion across multiple worksheets.
Thus A2 = vlookup(A1;sheetB!$C:$H;false)+
vlookup(A1;sheetC!$C:$H;false) + vlookup(A1;sheetD!$C:$H;false)
+....... + vlookup(A1;sheetX!$C:$H;false).
X is variable, meaning that sheets may be added or deleted.
Note that the arguments remain constant except for the sheets where
the values are to be looked up.
I don't think this can be handled with any combination of worksheet
functions. Is that right ?
Can anyone help me out here with the most efficient code ?

Thank you very much in advance.
Herman