Joshua earned an MBA from USF and writes mostly about software and technology.
Here I discuss how you can randomly have a message appear with the use of a shortcut key. There are no macros or buttons involved—just one formula and two columns of data. I tried this process recently because a friend ask me if it was possible. Well, it is and I did it.
Before I explain how to generate the random messages, here are some ideas for its use below:
- Kind word generator
- Select a person to go first
- Who picks up the check!
- What to work on
- What subject to study
I could go on with more examples, but now off to the tutorial.
The first thing need is to open Exel and create a list of messages vertically in a column. After that, assign a number to each message starting with the number 1.
Next, you will need to type in a formula. First, let’s look at the VLOOKUP function. This function will require four arguments shown below
- Lookup value – This will be a variable produced by a RANDOMBETWEEN function.
- The table array will be your whole range of data (both columns).
- Column index- This tells the function what value to return. The first column will be the column furthest to the left.
- Range lookup- This tells Exel whether you want an exact match.
The formula can be seen in its entirety below.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
The RANDOMBETWEEN function will serve as the lookup value. Entering the lowest number as the bottom and the highest as the top will allow the function to cycle through that range randomly. The syntax for this function can be seen below.
The table array will be all of the data used in both columns. You can see the table array being selected in the illustration below. The column index will be 2 because we need the value of the second column to be printed in the cell. Lastly, false is chosen so that the random number matches exactly the reference numbers in the table array.
If running a Mac computer use the shortcut command + r to get the formula to refresh and display an alternate message. If using a Windows macing simply use the F5 key.
You may elect to hide the columns that contain your data. This can be accomplished by selecting both columns followed by selecting the hide option.
A final tip is, if your message is too long for a cell, you may want to merge cells together and then use the wrap text option.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2022 Joshua Crowder