Find and return a value

[Mrs. Robinson]

I have a spreadsheet consisting of 6 worksheets named for each manager. On
the 7th worksheet, I have to return the rightmost value of a row which is
greater than 0 (cells generally have 3 or 4 digit numbers, followed by any
number of cells with "0" in them). The problem is that the data is never
consistently in the same row and rows are never consistently the same length,
but the data I need from every worksheet is in a row where the heading is
"No. of People".

ie. worksheet "John"

No. of people 123 234 345 456 256 0 0 0 0


How do I return the value of 256 to a cell on the 7th worksheet?
Thanks,
Mrs. Robinson

 

[Luke M]

You didn't say, but to make it "easier" to use, formula assumes sheet
name/person name is in cell A2. Can then copy down formula as desired.

Rather lengthy, but here's a tested formula:

=SUMPRODUCT((MAX((INDIRECT("'"&A2&"'!B"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0))<>0)*(COLUMN(INDIRECT("'"&A2&"'!B"&MATCH("No.
of People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0)))))=COLUMN(INDIRECT("'"&A2&"'!B"&MATCH("No.
of People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0))))*(INDIRECT("'"&A2&"'!B"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0))))

 

[Mrs. Robinson]

I didn't go far enough to explain how the data is presented on my spreadsheet

John 256
Joe 351
Terry 123

etc.

I get a reference error in the formula below, even though I put the
manager's names in A2.

Appreciate your help -