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

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

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

"JaB" wrote in message
...
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

There are 1160 characters in that formula. I believe the maximum is 256.
In that case could split the formula into 5 parts and then have a sixth
column to analyse the output and give your final answer? You could also
shorten the name of each sheet.

Regards.

Bill Ridgeway
Computer Solutions

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

Try this: One each worksheet (Assessment 1, Assessment 2, Assement 3) enter
a formula in a column or row that us can hide that will multiply each of the
cells on that worksheet - then where you want the fail / pass result enter
the if statement multipling the 3 results from above.

"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

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