0

Row=x where x is dynamic

Scott Clayton

November 11, 2021 01:36

None

Follow

I want to do a Rows=x NL function where x is a dynamic number sourced from a corresponding NL("Count") function

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  BobRoss December 16, 2021 18:15

  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.

• 0
  
  Scott Clayton December 17, 2021 00:06

  Thanks so much. I will give it a go