Array Functions/UDFs - blanking #NA fields
Hello,
I have developed a simple UDF that fetches some information from a database, and returns it as a bi-dimensional array. The number of rows can be from 10 to 20. If only 10 are returned, I get #N/A in all the empy cells. I want them to be empty if there is no value, and I don't want to change the UDF to receive the "max page size" (ie, 20).
I've been playing with ISERROR, IFERROR, ISERR, but was not lucky. Do you have any suggestions?
My current funct is:
{=MyGetData(a1)}
And I've tried things such as:
{ =IF(ISNA(C13:G33), " ", MyGetData(A1)) }
But I get a circular reference and the value is 0. C13:G33 is where the output is to be displayed.
Just played with it a bit - the naive IFERROR does not work because it's also producing a small array which then again results in N/As. However, if you use IFERROR on a mirrored range, it works:
| ={1,1;2,2} | ={1,1;2,2} | ={1,1;2,2} |
| ={1,1;2,2} | ={1,1;2,2} | ={1,1;2,2} |
| ={1,1;2,2} | ={1,1;2,2} | ={1,1;2,2} |
| | |
| =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") |
| =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") |
| =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") | =IFERROR(B4:D6, "") |
In this example, the bottom range (the IFERROR) is actually an array function using IFERROR on the range above it. That will give you blank cells.
I am using an UDF which loads the data from Datasource to Excel sheet.
As I am getting the Data from Database I dont know in advance the number of rows.
But while specifying the UDF in Excel I have to specify it for a specific range of rows and columns.
Is there a way to acheive this functionality.
Is it possible to progrmatically specify the range for UDF after getting the data from Database, using some custom coding?
Regards,
Sasya
