Unpredictable behavior with rand function

I am attempting to create a report that will pull inventory items, with qty on hand, that match the cycle count code we wish to check. Those items are data dumped on one sheet, and then I use the a combination of INDEX and RAND to select 5 random item numbers. Those numbers are pulled to the main report page and used in the NL query to target the specific 5 random items. Both running the report dynamically (either listing the array of item numbers or building the filter string) and I have tried statically (nl first command for each of the 5 selected). That static report works but only if I switch between design mode to report mode. Subsequent "refreshes" break report and return nothing. The dynamic report works on subsequent "refreshes" but doesn't always return the correct information as if the item no it is supposed to be looking at changes mid query.

Dataset with random selection image
random formula  =INDEX(D12:D13,RANDBETWEEN(12,ROWS($D$12:$D$13)),1)

and it works going from design to report mode

Try to just refresh the report though and

new set of numbers but no data.

My assumption is that the order of operations for how excel handles its functions and the way the jet formula functions are both evaluated is causing the issue.

Has anyone encountered an issue like this before?


Please sign in to leave a comment.