Hi
Is it possible to mix EXCEL functions and User Defined FUnctions in a formula
e.g In Cell "A", =COUNTA(datasheet()!$A:$A)
where datasheet is a UDF to get another sheet name and then COUNTA should use that sheetname to calculate the th
result

This formula is not working. Any suggestions

Thank
Kanan

Kanan,

I think you want to use the INDIRECT function to convert a text
string to an actual range reference. E.g.,

=COUNTA(INDIRECT(datasheet()&"!$A$A"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Kanan" wrote in message
...
Hi,
Is it possible to mix EXCEL functions and User Defined
FUnctions in a formula ?
e.g In Cell "A", =COUNTA(datasheet()!$A:$A)
where datasheet is a UDF to get another sheet name and then
COUNTA should use that sheetname to calculate the the
result.

This formula is not working. Any suggestions?

Thanks
Kanan

Hi
it is possible but in your case use
=COUNTA(INDIRECT("'" & datasheet() & "'!$A:$A"))


--
Regards
Frank Kabel
Frankfurt, Germany

"Kanan" schrieb im Newsbeitrag
...
Hi,
Is it possible to mix EXCEL functions and User Defined FUnctions in a
formula ?
e.g In Cell "A", =COUNTA(datasheet()!$A:$A)
where datasheet is a UDF to get another sheet name and then COUNTA
should use that sheetname to calculate the the
result.

This formula is not working. Any suggestions?

Thanks
Kanan

Hi Chip
small typo :-) you probably meant:
=COUNTA(INDIRECT(datasheet()&"!$A:$A"))

Though I would also add apostrophes to enclose the returned sheetname.


--
Regards
Frank Kabel
Frankfurt, Germany

"Chip Pearson" schrieb im Newsbeitrag
...
Kanan,

I think you want to use the INDIRECT function to convert a text
string to an actual range reference. E.g.,

=COUNTA(INDIRECT(datasheet()&"!$A$A"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Kanan" wrote in message
...
Hi,
Is it possible to mix EXCEL functions and User Defined
FUnctions in a formula ?
e.g In Cell "A", =COUNTA(datasheet()!$A:$A)
where datasheet is a UDF to get another sheet name and then
COUNTA should use that sheetname to calculate the the
result.

This formula is not working. Any suggestions?

Thanks
Kanan

It works!! Great! Thanks a lot for your fast response
kanan