Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP?
I need to know if this is possible.
I have a workbook with several worksheets. Each worksheet looks like (example): Worksheet #1 Column A Column B Column C Active Smith, Jane 10 Inactive Doe, John 12 Leave Brown, Lee 30 Worksheet #2 Column A Column B Column C Leave Doe, John 5 Leave Brown, Lee 15 Active Smith, Jane 40 What I need is the sum of all entries in column C from all worksheets (by row). And I need these numbers to stay with the person. The problem is that the person's name is not in the same row on every worksheet. So the 'totals' worksheet would look like this: Column A Column B Smith, Jane 50 Brown, Lee 45 Doe, John 17 I hope this makes sense. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP?
How many is "several" worksheets? How many rows in each of these
sheets? Assuming you have 3 sheets plus the totals sheet, here is one way of doing it - enter this in B2 of the totals sheet: =IF(ISNA(VLOOKUP(A2,Sheet1!B$2:C$100,2,0)), 0,VLOOKUP(A2,Sheet1!B$2:C $100,2,0)) + IF(ISNA(VLOOKUP(A2,Sheet2!B$2:C$100,2,0)), 0,VLOOKUP(A2,Sheet2!B$2:C$100,2,0)) + IF(ISNA(VLOOKUP(A2,Sheet3!B$2:C $100,2,0)), 0,VLOOKUP(A2,Sheet3!B$2:C$100,2,0)) All one formula - be wary of spurious line-breaks. I've assumed that you have data up to row 100 in each sheet, so change this if you have more. Hopefully you can see how to extend it if you have more sheets - just add: +if(isna(vlookup(...Sheet4...)),0,vlookup(...Sheet 4...)) to the end of the formula for a 4th sheet, and so on. Copy the formula down column B of the totals sheet, as required. Hope this helps. Pete On Dec 7, 12:41 am, smooney wrote: I need to know if this is possible. I have a workbook with several worksheets. Each worksheet looks like (example): Worksheet #1 Column A Column B Column C Active Smith, Jane 10 Inactive Doe, John 12 Leave Brown, Lee 30 Worksheet #2 Column A Column B Column C Leave Doe, John 5 Leave Brown, Lee 15 Active Smith, Jane 40 What I need is the sum of all entries in column C from all worksheets (by row). And I need these numbers to stay with the person. The problem is that the person's name is not in the same row on every worksheet. So the 'totals' worksheet would look like this: Column A Column B Smith, Jane 50 Brown, Lee 45 Doe, John 17 I hope this makes sense. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP?
=SUM(SUMIF(INDIRECT("Sheet"&{1,2}&"!B1:B100"),A1,I NDIRECT("Sheet"&{1,2}&"!C1:C100")))
"smooney" wrote: I need to know if this is possible. I have a workbook with several worksheets. Each worksheet looks like (example): Worksheet #1 Column A Column B Column C Active Smith, Jane 10 Inactive Doe, John 12 Leave Brown, Lee 30 Worksheet #2 Column A Column B Column C Leave Doe, John 5 Leave Brown, Lee 15 Active Smith, Jane 40 What I need is the sum of all entries in column C from all worksheets (by row). And I need these numbers to stay with the person. The problem is that the person's name is not in the same row on every worksheet. So the 'totals' worksheet would look like this: Column A Column B Smith, Jane 50 Brown, Lee 45 Doe, John 17 I hope this makes sense. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |