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("N o. of
People",INDIRECT("'"&A2&"'!A:A"),0))<0)*(COLUMN(I NDIRECT("'"&A2&"'!B"&MATCH("No.
of People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("N o. of
People",INDIRECT("'"&A2&"'!A:A"),0)))))=COLUMN(IND IRECT("'"&A2&"'!B"&MATCH("No.
of People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("N o. of
People",INDIRECT("'"&A2&"'!A:A"),0))))*(INDIRECT(" '"&A2&"'!B"&MATCH("No. of
People",INDIRECT("'"&A2&"'!A:A"),0)&":IV"&MATCH("N o. of
People",INDIRECT("'"&A2&"'!A:A"),0))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mrs. Robinson" wrote:

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