Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining VLOOKUPS?
Hello,
I am trying to tally the fields in several sheets of the same workbook. I can use either hidden fields or link one cell to a hidden sheet with several lookups, but would really like to put it all in one cell. I've put an example below of what I would like to do. Is there an easy way to go about this? I'd like to tally the cells of 7 sheets manytimes over. Thank you =(VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-3'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-5'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-6'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-8'!$C$4:$J$25, 5, FALSE)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining VLOOKUPS?
PT40 wrote...
I am trying to tally the fields in several sheets of the same workbook. I can use either hidden fields or link one cell to a hidden sheet with several lookups, but would really like to put it all in one cell. I've put an example below of what I would like to do. Is there an easy way to go about this? I'd like to tally the cells of 7 sheets manytimes over. .... You have several more left parentheses than right parentheses, so I've removed the unnecessary ones. =VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-3'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-5'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-6'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-8'!$C$4:$J$25, 5, FALSE) Your ranges aren't too big, and if there were only one instance of the value of C4 in the first columns of each range on the different worksheets, you could use =SUM(SUMIF(INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7","2-8"} &"'!C4:C25"),C4,INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7", "2-8"}&"'!G4:G25"))) Better, enter the worksheet names in a range, name the range W, and shorten this formula to =SUM(SUMIF(INDIRECT("'"&W&"'!C4:C25"),C4,INDIRECT( "'"&W&"'!G4:G25"))) If your ranges were big or you could have multiple instances of the C4 value in some of these ranges, summing separate VLOOKUP calls is optimal. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining VLOOKUPS?
Removing the parenthesis worked. Thanks much
"Harlan Grove" wrote: PT40 wrote... I am trying to tally the fields in several sheets of the same workbook. I can use either hidden fields or link one cell to a hidden sheet with several lookups, but would really like to put it all in one cell. I've put an example below of what I would like to do. Is there an easy way to go about this? I'd like to tally the cells of 7 sheets manytimes over. .... You have several more left parentheses than right parentheses, so I've removed the unnecessary ones. =VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-3'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-5'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-6'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE) +VLOOKUP(C4, '2-8'!$C$4:$J$25, 5, FALSE) Your ranges aren't too big, and if there were only one instance of the value of C4 in the first columns of each range on the different worksheets, you could use =SUM(SUMIF(INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7","2-8"} &"'!C4:C25"),C4,INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7", "2-8"}&"'!G4:G25"))) Better, enter the worksheet names in a range, name the range W, and shorten this formula to =SUM(SUMIF(INDIRECT("'"&W&"'!C4:C25"),C4,INDIRECT( "'"&W&"'!G4:G25"))) If your ranges were big or you could have multiple instances of the C4 value in some of these ranges, summing separate VLOOKUP calls is optimal. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUPS | Excel Discussion (Misc queries) | |||
Vlookups | Excel Worksheet Functions | |||
vlookups | Excel Discussion (Misc queries) | |||
Vlookups | Excel Discussion (Misc queries) | |||
vlookups | Excel Worksheet Functions |