If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup across multiple sheets
I am trying to build a formula that will do a vlookup
across multiple sheets and give me the sum of the results in a summary page. Here is what I have tried. =SUM(VLOOKUP(B7,'1:31'!B736,3,FALSE)) I have tried other variants of the this including putting the sheets ('1:31')in front of the vlookup, and can not get it to work. I would appreciate any help I can get. B. |
#2
|
|||
|
|||
Vlookup across multiple sheets
Hi Brian
if your sheets are really named 1 to 31 try the following formula to sum all values in column D for which column B equals B7 =SUMPRODUCT(SUMIF(INDIRECT("'" & ROW(1:31) & "'!B7:B36"), B7,INDIRECT("'" & ROW(1:31) & "'!D736"))) -- Regards Frank Kabel Frankfurt, Germany Brian wrote: I am trying to build a formula that will do a vlookup across multiple sheets and give me the sum of the results in a summary page. Here is what I have tried. =SUM(VLOOKUP(B7,'1:31'!B736,3,FALSE)) I have tried other variants of the this including putting the sheets ('1:31')in front of the vlookup, and can not get it to work. I would appreciate any help I can get. B. |
#3
|
|||
|
|||
Vlookup across multiple sheets
ROW(1:31) yields a non-robust formula.
"Frank Kabel" wrote in message ... Hi Brian if your sheets are really named 1 to 31 try the following formula to sum all values in column D for which column B equals B7 =SUMPRODUCT(SUMIF(INDIRECT("'" & ROW(1:31) & "'!B7:B36"), B7,INDIRECT("'" & ROW(1:31) & "'!D736"))) -- Regards Frank Kabel Frankfurt, Germany Brian wrote: I am trying to build a formula that will do a vlookup across multiple sheets and give me the sum of the results in a summary page. Here is what I have tried. =SUM(VLOOKUP(B7,'1:31'!B736,3,FALSE)) I have tried other variants of the this including putting the sheets ('1:31')in front of the vlookup, and can not get it to work. I would appreciate any help I can get. B. |
#4
|
|||
|
|||
Vlookup across multiple sheets
Aladin Akyurek wrote:
ROW(1:31) yields a non-robust formula. Appreciate your comment - could you explain why this is non-robust and what alternative you would use :-) Did you mean to replace ROW(1:31) with ROW($1:$31) Regards Frank |
#5
|
|||
|
|||
Vlookup across multiple sheets
Inserting one or more rows before the formula row would cause havoc. Users
like to beautify their spreadsheets and will fail to notice/see the incorrect results due to a formula, broken by their beuatifying actions. The idiom to use is: ROW(INDIRECT("1:31")) even better: ROW(INDIRECT("1:"&ExpressionThatComputesN)) even if all this is going to cost more. "Frank Kabel" wrote in message ... Aladin Akyurek wrote: ROW(1:31) yields a non-robust formula. Appreciate your comment - could you explain why this is non-robust and what alternative you would use :-) Did you mean to replace ROW(1:31) with ROW($1:$31) Regards Frank |
#6
|
|||
|
|||
Vlookup across multiple sheets
Try
ROW(INDIRECT("1:31")) that way the sheet name won't change if for instance a row is inserted above the row where the formula is. Any row(1:31) or row($1:$31) will change to row(2:32) while the one with indirect will stay hard coded -- Regards, Peo Sjoblom "Frank Kabel" wrote in message ... Aladin Akyurek wrote: ROW(1:31) yields a non-robust formula. Appreciate your comment - could you explain why this is non-robust and what alternative you would use :-) Did you mean to replace ROW(1:31) with ROW($1:$31) Regards Frank |
#7
|
|||
|
|||
Vlookup across multiple sheets
Hi Aladin
thanks for the addition (forgot about this) -- Regards Frank Kabel Frankfurt, Germany Aladin Akyurek wrote: Inserting one or more rows before the formula row would cause havoc. Users like to beautify their spreadsheets and will fail to notice/see the incorrect results due to a formula, broken by their beuatifying actions. The idiom to use is: ROW(INDIRECT("1:31")) even better: ROW(INDIRECT("1:"&ExpressionThatComputesN)) even if all this is going to cost more. "Frank Kabel" wrote in message ... Aladin Akyurek wrote: ROW(1:31) yields a non-robust formula. Appreciate your comment - could you explain why this is non-robust and what alternative you would use :-) Did you mean to replace ROW(1:31) with ROW($1:$31) Regards Frank |
Thread Tools | |
Display Modes | |
|
|