Prevent (Volatile) RANDBETWEEN Auto-ReCalculation WITHOUT using VBA

[DeepBlackHole]

In my Excel 2013 workbook of four worksheets, one worksheet has a column of about 20 cells in each of which I need to randomly choose (i.e., display) one of four pre-defined short text strings. The random selection of which string must be done ONLY when the spreadsheet is opened and once chosen/displayed, the chosen text string remains constant regardless of other activity or editing throughout that user session.

I have been playing with RANDBETWEEN but since that is a volatile function, my random string choices recalculate every time a change is made anywhere else in the entire workbook, which is a full-blown dealbreaker.

So far, each of these 20 cells contains this same formula:

=INDEX(Sheet1!$A$1:$A$4,RANDBETWEEN(1,4),1)

where cells A1:A4 on Sheet1 contain (for sake of argument) the text strings Apple, Banana, Lime and Pear

This formula does EXACTLY what I want it to do, but it (not surprisingly) displays a new random string from among those four in every one of the 20 cells every time I “touch the keyboard”.

The complications:

My workbook is stored, maintained and for use only on my employer’s network, in which the system administrators tightly control user-permitted activities. This workbook must be shared with others on this same network, so I cannot ensure that any recipient needing to see and use it will have disabled Automatic Recalculation (set it to Manual) on their machine or in their profile. I must assume that every recipient will have Auto Recalculate set to "on" which is the default. On top of that, all user VBA access is forbidden - users cannot enter or edit modules, UDFs, etc, so I’m stuck with finding a pre-defined Excel function or combination of functions.

Is this even possible?

[Bernie Deitrick]

I would create a hidden sheet with sufficient values - thousands of values in groups of twenty down a column - to allow multiple days of values to be extracted based on dates, with a formula like

=INDEX(HiddenSheet!$A:$A,(TODAY()-DATE(2017,11,28))*20+ROW(A1))

copied to 19 other cells. Replace +ROW(A1) with +COLUMN(A1) if you are copying across rather than down.


If you have multiple users, you could use a column of values for each user, too, with a formula like

=INDEX(HiddenSheet!$A:$Z,(TODAY()-DATE(2017,11,28))*20+ROW(A1),MATCH(username,UserList,False))

Further, you should get somebody in IT with a brain - disabling macro use is like giving a fully stocked toolbox to a carpenter and saying "you can only use the hammer no matter what"

[tim201110]

maybe without formulas?
analysis ToolPak
random number generation