Formula change from cell reference to count

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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"))
 
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.
 
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.
 
You need to use some address in the cell("address",xx) function.

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