View Single Post
  #4   Report Post  
Dave O
 
Posts: n/a
Default

Here's one way to do it: add a column next to your list of 5000
inventory items; highlight the cells and assign a number starting at 1,
using the Fill Series function.

For your cycle count items, enter this formula
RANDBETWEEN(1,5000)
.... which generates a random number between 1 and 5000. Then use that
random number as the argument in a VLOOKUP() function, which will
display the item from the inventory list that is associated with that
random number.

Some notes:
~You may need to load the Analysis ToolPak add-in
~Every time the spreadsheet recalculates (this can be as often as every
time you press the Enter key) the random numbers will change, so you
may want to convert them to values.
~Check the random numbers for duplicates
~Because items can be added to or deleted from the inventory list, you
may want to use MIN() and MAX() functions within the RANDBETWEEN()
function to draw accurate random numbers from the inventory list.