If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
another IF query ......
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 ........... |
#12
|
|||
|
|||
another IF query ......
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 ........... |
#13
|
|||
|
|||
another IF query ......
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 ........... |
#14
|
|||
|
|||
another IF query ......
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 ........... |
#15
|
|||
|
|||
another IF query ......
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 ........... |
#16
|
|||
|
|||
another IF query ......
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 ........... |
#17
|
|||
|
|||
another IF query ......
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 ........... |
#18
|
|||
|
|||
another IF query ......
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 ........... |
#19
|
|||
|
|||
another IF query ......
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 ........... |
#20
|
|||
|
|||
another IF query ......
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 ........... |
Thread Tools | |
Display Modes | |
|
|