Excel =SUBNM Limitations

[Adam]

Hello,

I was going to ask the question is there a limit to the number of items that can be shown on an Excel SUBNM list. However, I just figured, why don't I try instead of ask.

I pointed SUBNM against the longest dimension I have, in this case it's }LineNumber.
First good news is that it displays all 10,000 items. The second good news is I don't believe my longest dimension will get to 10,000 items. (Famous last words?)

Only question I now have left, any performance hits in Excel anyone's seen with pointing SUBNM against such a long dimension? I'm using PA for Excel.

[jim wood]

If you're combining the SubNM with other large amounts of formulas you may hit some performance issues down to just volume. I've only ever tried doing this kind of thing with old web forms rather than the newer stuff though.

[gtonkin]

When using PAfE, the SUBNM function is rendered as a data validation. The data validation uses a range like "cafe_validation_temp" (hidden named range).
This range is located on the very hidden Cognos_Office_Connection_Cache sheet and values start at row 2.

I guess you will run out of options when you have more than 1,048,575 elements in your subset as there are not enough rows to hold these elements for the data validation.

But like Jim points out, performance will probably be horrendous way before this kind of volume. Also note that the values in the hidden sheet are over-written each time you go to another SUBNM - there is obviously overhead in clearing out what is there, getting the list of elements related to this SUBNM, updating the named range etc. etc. This is also why you notice a lag when navigating and activating a cell with a SUBNM formula.