excel functions and User defined functions

K

Kanan

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
 
C

Chip Pearson

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 said:
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
Click to expand...
COUNTA should use that sheetname to calculate the the
 
F

Frank Kabel

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


--
Regards
Frank Kabel
Frankfurt, Germany

Kanan said:
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
Click to expand...
should use that sheetname to calculate the the
 
F

Frank Kabel

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

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