=if(counta) problem

[paul w]

i have a work book with 3 sheets named pages 1, 2 and 3, in (page1), cell AU1, i have a formula that changes the text from "1 of 1" to "1 of 2", if data is entered in (page2).
=IF(COUNTA('PAGE2'!D6:X38)=0,"1 OF 1","1 OF 2")

i need it to check (page 3) same cell range, and return "1 of 3" if data is entered.

 

[Willmonbo]

try this and let me know if it works

=IF(COUNTA('PAGE2'!D6:X38)=0,"1 OF 1",IF(COUNTA('PAGE3'!D6:X38)>0,"1 OF 3"),"1 OF 2")

 

[paul w]

thanks for your reply but error message "to many arguments for this functon"

 

[Willmonbo]

Oops, made a mistake, here is a correction

=IF(COUNTA('PAGE2'!D6:X38)=0,"1 OF 1",IF(COUNTA('PAGE3'!D6:X38)>0,"1 OF 3","1 OF 2"))

sorry about that.

 

[paul w]

thanks it sort of works, but if there is no data entered in any pages, page 1 reads "1of3 instead of "1of1" also if i enter data in page 2 it doesnt change,

 

[Willmonbo]

are your sheets really named Page1, page2, if not just change the formula to say sheet in place of page and also take out the ' ' just leave it at sheet! or page!, and let me know if it works.

 

[paul w]

Sorry, typed it in by hand and it worked ok, just 1 prob forgot to say page 2 needs to change from "2 of 2" to "2 of 3" if all pages are used.

 

[Willmonbo]

so do you have currently a formula in page 2, or do you want me to create it for you from the start?

 

[paul w]

sorry i do,

=if(COUNTA(D6:X38)=0,"","2 OF 2")

But all this does is replace the empty cell with the text, its ok if i only use 2 pages but if i use the third, then the text would need to change to "3 OF 3".
paul

 

[Willmonbo]

=if(COUNTA(D6:X38)=0,"",IF(COUNTA(D6:X38)=3,"1 OF 3","1 OF 2"))

looks right, let me know if it doesn't work though.