I want to do a Rows=x NL function where x is a dynamic number sourced from a corresponding NL("Count") function
2 comments
-
BobRoss This is extremely difficult to do because of the order of operations of Jet. It will evaluate the NL Rows function before it will evaluate the NL Count so at run time, that NL Count will always come back as 1.
You have to trick the system essentially by using the order of operations of the replicators (Sheets->Columns->Rows) and order of evaluation within Excel (left-most sheet first, then moving to the right sheet by sheet).
In the past, I was able to accomplish what you want by creating a columns replicator on a new worksheet within your workbook and making it the first worksheet. Instead of a NL(Count), use NL(Columns) to create the list of the unique values of the table. Then use Excel's COUNTA function to count the number of values (range of replicator and blank cell to the right) that are returned and link to the cell with that function within your Rows=X function.
-
Scott Clayton Thanks so much. I will give it a go