Then you need to do a LOOKUP of some sort to determine if there is a zero
value. Because of the number of worksheets involved and length of worksheet
name, I would perform this lookup per worksheet, then take that data and
determine your pass/fail on the worksheet that you need the data.

Les

"Dave F" wrote:

Why not count the 0 values on each individual sheet and then run a formula
which sums those counts. If the count is 0, i.e., there is at least on 0
value, then "FAIL", else "PASS"

Dave
--
Brevity is the soul of wit.


"JaB" wrote:

Hopefully (!) its looking through those cells to see if any of them have a
zero in it. If it finds one I want it to return the message 'FAIL', if not,
'PASS'.

"WLMPilot" wrote:

Am I reading this wrong or are you multiply all the cells together and if it
= 0, "Fail"?
I don't think it is searching for a specific value, but I could be wrong.

Les

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks