Formula change from cell reference to count

[J.W. Aldridge]

Formula works as is:

If sheet exist matching the name in A45, then look at K39 on that
sheet and return the value.

I need to ammend to say:

If sheet exist with matching the name in A45, then count the number of
times the name is listed in Column N (or range n2:n500) on that
sheet.

=IF(ISERROR(CELL("address",INDIRECT("'" & A45& "'!k$39")))," ",INDIRECT
("'" &A45& "'!k$39"))

 

[Dave Peterson]

First, I returned "" instead of " " (I don't like spaces!).

=IF(ISERROR(CELL("address",INDIRECT("'" & A45& "'!a1"))),"",
COUNTIF(INDIRECT("'" &A45& "'!n:n"),A45))

And I changed the K$39 to A1. It seems more natural to me and really doesn't
matter since you're only checking for the existence of the sheet.

 

[J.W. Aldridge]

Thanx.

But actually, I dont need the A1 reference....

I just need it to find the sheet if it exist, and return the count of
the specified cell value (a45) in column N.

 

[Dave Peterson]

You need to use some address in the cell("address",xx) function.

A1 seemed like a more natural choice to me than K$39.