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 ...........

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

You are an absolute star - thank you soooooo much for your helkp and
patience. If im ever in your neck of the woods, I'll buy you a beer.

Take care and thanks again, although Im sure I'll be back with another
problem soon!!!

Carol
x

"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

You're most welcome, it's our pleasure to be of help........please feel free
to come back anytime, and thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

You are an absolute star - thank you soooooo much for your helkp and
patience. If im ever in your neck of the woods, I'll buy you a beer.

Take care and thanks again, although Im sure I'll be back with another
problem soon!!!

Carol
x

"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work out more or
less what it means now - the only problem is that having just filled in a
load of data on the real thing, double and treble checked the formula - its
now coming back with a value of zero (whereas before it was coming back with
one) even though i have got two definate follow up appointments that should
show on the summary sheet. I dont know whether to cry or hang myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Fear not...........thi difficulty is with the logic, not the process. We
must first define EXACTLY what you wish to have happen, then re-write the
formula.........

Here's one possible try......see if it does anything right. Otherwise, try
to verbalize your needs in regular words.

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

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work out more or
less what it means now - the only problem is that having just filled in a
load of data on the real thing, double and treble checked the formula - its
now coming back with a value of zero (whereas before it was coming back with
one) even though i have got two definate follow up appointments that should
show on the summary sheet. I dont know whether to cry or hang myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

OK thats it - i give up!!! im going for the hanging myself option now.
Didnt like that one at all. I'll write in words what i need:

IF "there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value,
AND,
there are NO cells within the range 'Test Data'!Z26:Z43 that contain any
values" OR,
there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",
then return that total number of values.

ie: If column AD/AE is populated and column Z and/or column AB is populated,
remove them from the count. count how many 'populated' values are left in
AD/AE and give me the total.
I THINK!

I hate logic!!!


"CLR" wrote:

Fear not...........thi difficulty is with the logic, not the process. We
must first define EXACTLY what you wish to have happen, then re-write the
formula.........

Here's one possible try......see if it does anything right. Otherwise, try
to verbalize your needs in regular words.

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

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work out more or
less what it means now - the only problem is that having just filled in a
load of data on the real thing, double and treble checked the formula - its
now coming back with a value of zero (whereas before it was coming back with
one) even though i have got two definate follow up appointments that should
show on the summary sheet. I dont know whether to cry or hang myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

No hanging allowed.........never give up!!!
Maybe this one...........

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

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

OK thats it - i give up!!! im going for the hanging myself option now.
Didnt like that one at all. I'll write in words what i need:

IF "there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value,
AND,
there are NO cells within the range 'Test Data'!Z26:Z43 that contain any
values" OR,
there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",
then return that total number of values.

ie: If column AD/AE is populated and column Z and/or column AB is populated,
remove them from the count. count how many 'populated' values are left in
AD/AE and give me the total.
I THINK!

I hate logic!!!


"CLR" wrote:

Fear not...........thi difficulty is with the logic, not the process. We
must first define EXACTLY what you wish to have happen, then re-write the
formula.........

Here's one possible try......see if it does anything right. Otherwise, try
to verbalize your needs in regular words.

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

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work out more or
less what it means now - the only problem is that having just filled in a
load of data on the real thing, double and treble checked the formula - its
now coming back with a value of zero (whereas before it was coming back with
one) even though i have got two definate follow up appointments that should
show on the summary sheet. I dont know whether to cry or hang myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Oh dear - no joy with that one either - doesnt give me anything at all. Is
it allowable that i fax or (e)mail you a copy of my spreadsheet so you can
actually see what i need to do - im wondering if im making the issue more
complicated by not explaining it very well. I feel really awful keep asking
you for help - i know you said you dont mind but even so ......

"CLR" wrote:

No hanging allowed.........never give up!!!
Maybe this one...........

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

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

OK thats it - i give up!!! im going for the hanging myself option now.
Didnt like that one at all. I'll write in words what i need:

IF "there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value,
AND,
there are NO cells within the range 'Test Data'!Z26:Z43 that contain any
values" OR,
there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",
then return that total number of values.

ie: If column AD/AE is populated and column Z and/or column AB is populated,
remove them from the count. count how many 'populated' values are left in
AD/AE and give me the total.
I THINK!

I hate logic!!!


"CLR" wrote:

Fear not...........thi difficulty is with the logic, not the process. We
must first define EXACTLY what you wish to have happen, then re-write the
formula.........

Here's one possible try......see if it does anything right. Otherwise, try
to verbalize your needs in regular words.

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

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work out more or
less what it means now - the only problem is that having just filled in a
load of data on the real thing, double and treble checked the formula - its
now coming back with a value of zero (whereas before it was coming back with
one) even though i have got two definate follow up appointments that should
show on the summary sheet. I dont know whether to cry or hang myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL to
ZERO.....in other words, there IS at least one cell in that range that
contains a value........likewise for the other similar versions of the COUNTA
term used in the formula.....it COUNTS the number of cells that meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one, just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in words how the
formula actually breaks down - that way i might have a vague chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change the '1' for '0' at
the end will that give me zero in my summary sheet if there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the formula......the very
last part says.......if the COUNTA('Test Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a different sheet and
replaced the 'test data' bit and it sort of works. The only snag now is that
the data sheet had nothing in it at all but the summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........

Of course you can email me a file, but you must NOT post it to the
group.....send it here at this computer....and I am leaving for work now and
won't be back at this computer for 11 hours....about 5pm my time here in St.
Petersburg Florida...........I will look at it this evening and get back to
you....



remove the obvious from the above email addy

And, when you say this new formula "doesn't give you anything at all", does
that mean you only get a blank cell, no matter how you might populate the
different ranges?

Looking forward to getting your file.........
Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote in
message ...
Oh dear - no joy with that one either - doesnt give me anything at all.
Is
it allowable that i fax or (e)mail you a copy of my spreadsheet so you can
actually see what i need to do - im wondering if im making the issue more
complicated by not explaining it very well. I feel really awful keep
asking
you for help - i know you said you dont mind but even so ......

"CLR" wrote:

No hanging allowed.........never give up!!!
Maybe this one...........

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

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

OK thats it - i give up!!! im going for the hanging myself option
now.
Didnt like that one at all. I'll write in words what i need:

IF "there is at least one cell within the range 'Test Data'!AD26:AE43
that
contains a value,
AND,
there are NO cells within the range 'Test Data'!Z26:Z43 that contain
any
values" OR,
there are NO cells within the range 'Test Data'!AB26:AB43 that contain
any
values",
then return that total number of values.

ie: If column AD/AE is populated and column Z and/or column AB is
populated,
remove them from the count. count how many 'populated' values are
left in
AD/AE and give me the total.
I THINK!

I hate logic!!!


"CLR" wrote:

Fear not...........thi difficulty is with the logic, not the
process. We
must first define EXACTLY what you wish to have happen, then
re-write the
formula.........

Here's one possible try......see if it does anything right.
Otherwise, try
to verbalize your needs in regular words.

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

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

Im back!!!!!

sorry ........

actually thats really much clearer than you thought - i can work
out more or
less what it means now - the only problem is that having just
filled in a
load of data on the real thing, double and treble checked the
formula - its
now coming back with a value of zero (whereas before it was coming
back with
one) even though i have got two definate follow up appointments
that should
show on the summary sheet. I dont know whether to cry or hang
myself!

Carol
x

"CLR" wrote:

Ok, it's not easy to be real clear on t hings like this, but
I'll try.......

The formula is

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

First, the term COUNTA('Test Data'!AD26:AE43)<0, means the
total COUNT of
any cells within the range of AD26 through AB43 being NOT EQUAL
to
ZERO.....in other words, there IS at least one cell in that
range that
contains a value........likewise for the other similar versions
of the COUNTA
term used in the formula.....it COUNTS the number of cells that
meets it's
qualifications.

Therefore, the formula says that

IF, either of the following conditions are true,

"there is at least one cell within the range 'Test
Data'!AD26:AE43 that
contains a value, AND, there are NO cells within the range 'Test
Data'!Z26:Z43 that contain any values"
OR,
"there are NO cells within the range 'Test Data'!AB26:AB43 that
contain any
values",

then return the result of 1,

otherwise, return only a blank cell.

And hey, if you run into difficulty formulating the next one,
just come on
back........

Vaya con Dios,
Chuck, CABGx3




"englishrose4719" wrote:

Just another thought - would you be able to explain to me in
words how the
formula actually breaks down - that way i might have a vague
chance of
working it out for myself next time?

thanks again
Carol


"CLR" wrote:

It should, you could even change it to this if you
wished..........

=IF(OR(AND(COUNTA('Test Data'!AD26:AE43)<0,COUNTA('Test
Data'!Z26:Z43)=0),COUNTA('Test Data'!AB26:AB43)=0),"No
follow-up appointments
due","")

Note that if you are using TEXT, that you have to enclose it
in the
double-quotes.

Vaya con Dios,
Chuck, CABGx3



"englishrose4719" wrote:

Excellent!! - i think youve cracked it - so if i change
the '1' for '0' at
the end will that give me zero in my summary sheet if
there are no follow up
appointments due then?


thanks again
Carol

"CLR" wrote:

The result you're seeing is the expected result of the
formula......the very
last part says.......if the COUNTA('Test
Data'!AB26:AB43)=0, then make the
result 1.....that can be changed of course to whatever
you would like

Vaya con Dios,
Chuck, CABGx3


"englishrose4719" wrote:

I think i love you!!!! Ive just tried it again on a
different sheet and
replaced the 'test data' bit and it sort of works.
The only snag now is that
the data sheet had nothing in it at all but the
summary sheet gives me a
total of 1. I dont know if thats better or worse!!!

Carol
x

"CLR" wrote:

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 ...........