Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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" wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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" 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 "herman" wrote in message m... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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)) BubBob wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of vlookups across all worksheets.
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. "herman" wrote in message om... 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" 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 "herman" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding VLOOKUPS across multiple worksheets | Excel Worksheet Functions | |||
Why can't I sum several vlookups ?? | Excel Worksheet Functions | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
VLOOKUPS | Excel Discussion (Misc queries) | |||
worksheets vlookups | Excel Worksheet Functions |