excel functions and User defined functions
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 |
excel functions and User defined functions
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 |
excel functions and User defined functions
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 |
excel functions and User defined functions
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 |
excel functions and User defined functions
It works!! Great! Thanks a lot for your fast response
kanan |
All times are GMT +1. The time now is 09:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com