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.

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 -

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.

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.

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 -

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 -