Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
Hi - I'm trying to do the following:
In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
Do you have a blank row after the total and then a new name? If so,
can you find the next name after Joe Smith (are they sorted?) and then come back two rows? MATCH will return the (relative) row in a range that a matched item is found on, or you can look through the whole column and thus return the absolute row. Hope this helps. Pete On Jan 14, 5:04*pm, Paul Peterson - Velox Consulting, LLC oft.com wrote: Hi - I'm trying to do the following: In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting.- Hide quoted text - - Show quoted text - |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
Is there some text or something that indicates its the total row? For
instance, if column A is the name, Column B is what he did or "Total", and Column C is the hours, you could do =SUMPRODUCT((A2:A100="Smith, Joe")*(B2:B100="Total")*(C2:C100)) If its just that the total row is the last row under that name (hours is in column B) =SUMPRODUCT((A2:A100="Smith, Joe")*(A2:A100<A3:A101)*(B2:B100)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Peterson - Velox Consulting, LLC" wrote: Hi - I'm trying to do the following: In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
thanks for the quick reply - I'll try this too.
"Luke M" wrote: Is there some text or something that indicates its the total row? For instance, if column A is the name, Column B is what he did or "Total", and Column C is the hours, you could do =SUMPRODUCT((A2:A100="Smith, Joe")*(B2:B100="Total")*(C2:C100)) If its just that the total row is the last row under that name (hours is in column B) =SUMPRODUCT((A2:A100="Smith, Joe")*(A2:A100<A3:A101)*(B2:B100)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Peterson - Velox Consulting, LLC" wrote: Hi - I'm trying to do the following: In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
thanks for the quick reply - I'll try this. "Pete_UK" wrote: Do you have a blank row after the total and then a new name? If so, can you find the next name after Joe Smith (are they sorted?) and then come back two rows? MATCH will return the (relative) row in a range that a matched item is found on, or you can look through the whole column and thus return the absolute row. Hope this helps. Pete On Jan 14, 5:04 pm, Paul Peterson - Velox Consulting, LLC oft.com wrote: Hi - I'm trying to do the following: In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for a number in a list
Pete, I just realized I don't know how to back up one row - can you help? "Pete_UK" wrote: Do you have a blank row after the total and then a new name? If so, can you find the next name after Joe Smith (are they sorted?) and then come back two rows? MATCH will return the (relative) row in a range that a matched item is found on, or you can look through the whole column and thus return the absolute row. Hope this helps. Pete On Jan 14, 5:04 pm, Paul Peterson - Velox Consulting, LLC oft.com wrote: Hi - I'm trying to do the following: In Cell B1, I have the value "Smith, Joe" In cell a4, I have a total number of hours for Joe Smith: 40. Depending on the detail hours reported, the total hours may be in a different line. For example, if Joe had 6 line items of detail, the total would be in line a6. So, I have to first find Joe Smith in the spreadsheet, then find the total line. Any ideas? Paul Peterson Velox Consulting.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching A List | Excel Worksheet Functions | |||
searching for a word(s) within a list | Excel Discussion (Misc queries) | |||
Searching for a contact without going through the whole list. | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
searching within list | Excel Discussion (Misc queries) |