How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Thanks Chuck - i tried it but now there is a VALUE error coming up.

Any ideas?
Carol


"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Copy and Paste your formula here, we'll take a look..........

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Thanks Chuck - i tried it but now there is a VALUE error coming up.

Any ideas?
Carol


"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

=IF(OR(AND('TEST DATA'!AD26:AE43<"",'TEST DATA'!Z26:Z43=""),'TEST
DATA'!AB26:AB43=""),1,"")

Thanks - sorry to be a pain - ive been trying to do this all day and my head
is spinning!!!

My end results need to fill in this on a summary sheet called SAR:

Ongoing Follow Ups New =
Ongoing Follow Ups Used =

and the data to be used is coming from separate worksheets called 1 - 31:

cell refs
Z26:Z43 (N) AB26:AB43 (N) AD26:AD43
AA26:AA43 (U) AC26:AC43 (U)

Dealt Hand Over Forward date
N U N U 14/05 etc

If there is a forward date then the customer will be an ongoing follow up
UNLESS there is a tick in the new or used Dealt or Handover boxes.

Ive got no nails left!!!!!

Carol



"CLR" wrote:

Copy and Paste your formula here, we'll take a look..........

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Thanks Chuck - i tried it but now there is a VALUE error coming up.

Any ideas?
Carol


"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Try this......

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),1,"")

All on one line, watch out for wordwrap

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:


=IF(OR(AND('TEST DATA'!AD26:AE43<"",'TEST DATA'!Z26:Z43=""),'TEST
DATA'!AB26:AB43=""),1,"")

Thanks - sorry to be a pain - ive been trying to do this all day and my head
is spinning!!!

My end results need to fill in this on a summary sheet called SAR:

Ongoing Follow Ups New =
Ongoing Follow Ups Used =

and the data to be used is coming from separate worksheets called 1 - 31:

cell refs
Z26:Z43 (N) AB26:AB43 (N) AD26:AD43
AA26:AA43 (U) AC26:AC43 (U)

Dealt Hand Over Forward date
N U N U 14/05 etc

If there is a forward date then the customer will be an ongoing follow up
UNLESS there is a tick in the new or used Dealt or Handover boxes.

Ive got no nails left!!!!!

Carol



"CLR" wrote:

Copy and Paste your formula here, we'll take a look..........

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Thanks Chuck - i tried it but now there is a VALUE error coming up.

Any ideas?
Carol


"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Hi Chuck

thanks for your answer, but i must be blonder than i thought!! I copied and
pasted it, made sure it only goes an one line, but im still getting nothing
in the box - not even a zero.

How dumb am i being?

thanks again
Carol

"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

I dunno, must be some problem with the transfer......I just re-tried copy and
pasteing the formula in to cell C1 on my sheet1 and I get a return of 1, and
considering there is no data in column A, this would be the correct return.

Maybe just try hand-typing the formula in place..........is your Sheet1 for
sure named Sheet1?

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Hi Chuck

thanks for your answer, but i must be blonder than i thought!! I copied and
pasted it, made sure it only goes an one line, but im still getting nothing
in the box - not even a zero.

How dumb am i being?

thanks again
Carol

"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

No, Sheet 1 is called test data, which is what youve got in your formula.
Im so sorry to be a flipping nuisance but i really am clueless!! Would the
fact that AD and AE are merged cells be causing the problem?

Ive copied and pasted what my sheet looks like

DEALT HANDOVER TO DATE
N U N U
16.5
1 24.6
1 14.5
1 11.7

Carol x

"CLR" wrote:

I dunno, must be some problem with the transfer......I just re-tried copy and
pasteing the formula in to cell C1 on my sheet1 and I get a return of 1, and
considering there is no data in column A, this would be the correct return.

Maybe just try hand-typing the formula in place..........is your Sheet1 for
sure named Sheet1?

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Hi Chuck

thanks for your answer, but i must be blonder than i thought!! I copied and
pasted it, made sure it only goes an one line, but im still getting nothing
in the box - not even a zero.

How dumb am i being?

thanks again
Carol

"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........

Ignore my previous, I was looking at the wrong formula..........

I dunno...........I just re-tested this one and it worked fine.......

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),1,"")

The only other thing I can think of is your SheetName might not be EXACTLY
the same as that in the formula.........might be a leading space or two
spaces in between the words or something like that........Try changing it's
name and then changing it back real carefully..........and don't worry about
coming back and coming back......we're here to try to help you get the
answer........

Vaya con Dios,
Chuck, CABGx3





"englishrose4719" wrote:

No, Sheet 1 is called test data, which is what youve got in your formula.
Im so sorry to be a flipping nuisance but i really am clueless!! Would the
fact that AD and AE are merged cells be causing the problem?

Ive copied and pasted what my sheet looks like

DEALT HANDOVER TO DATE
N U N U
16.5
1 24.6
1 14.5
1 11.7

Carol x

"CLR" wrote:

I dunno, must be some problem with the transfer......I just re-tried copy and
pasteing the formula in to cell C1 on my sheet1 and I get a return of 1, and
considering there is no data in column A, this would be the correct return.

Maybe just try hand-typing the formula in place..........is your Sheet1 for
sure named Sheet1?

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Hi Chuck

thanks for your answer, but i must be blonder than i thought!! I copied and
pasted it, made sure it only goes an one line, but im still getting nothing
in the box - not even a zero.

How dumb am i being?

thanks again
Carol

"CLR" wrote:

Try this.......

=IF(OR(AND(Sheet1!A20<"",Sheet1!A16=""),Sheet1!A1 7=""),1,"")

Just change the Sheet1 to whatever sheet number you want the calculations
done for.

hth
Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

How do I work this one out please?

IF 'A20' is not null AND 'A16' is null OR 'A17' is null then COUNT result in
'A14' on a different spreadsheet!!!!!

help me please ...........