![]() Now, with the help of the Lambda function, we can make a random name generator that won’t refresh the result upon changes in Google Sheets. It causes changes in the random number used in the Index for offset. The issue was associated with the Randbetween volatile function, which refreshes upon changes in Sheet. When I first wrote the above formula back in 2018, if I remember correctly, there was no formula option to achieve this. How Do I Pick a Random Name and Make It Static in Google Sheets? Replace “Option 1” with “Yes” and “Option 2” with “No.”.We only want the random name generator to refresh when we change the value in cell B3.īefore learning that, let’s create a drop-down in cell B3.Ī drop-down in cell B3 will help you to switch between “Yes” or “No” easily. The above code is still prone to refresh upon changes in other cells.ĭon’t worry! We will learn below how to pick a random name from a list and make it static (doesn’t refresh) in Google Sheets. It will only execute if the text in cell B3 is “Yes.”įormula_3: =if($B$3="Yes",iferror(index(A2:A,randbetween(1,counta(A2:A)))),"")īut remember one thing! You have entered “Yes” in cell B3. If you want to set a trigger, use the following formula instead. Use the IFERROR function to return null in case of an error.įormula_2: =iferror(index(A2:A,randbetween(1,counta(A2:A)))) If A2:A is blank, the above formula will return an error. Range A2:A should contain your list of names. You can use the following formula in any cell other than range A2:A to get a random name from the given range (long list).įormula_1: =index(A2:A,randbetween(1,counta(A2:A))) Formula to Pick a Random Name from a Long List in Google Sheets It will update (refresh) when you make any changes to the Sheet. These three functions are enough to pick a random name from a list in Google Sheets, but the result won’t be static. INDEX: It will offset the random number returned by Randbetween in the list and get the content of that cell.COUNTA: We will use it to get the total number of names in the list to feed the highest value in the above function.The number 1 will be the lowest, and 13 (count of names) will be the highest. RANDBETWEEN: We will use it to generate a random integer between two values, inclusive.Here are the three main functions that we will use and their role. ![]() Selecting a name randomly from this range is simple. Get More Unique Names When Dragging Down the Formulaīasic Formula Logic: Random Number OffsetĪssume we have the names of 13 students in cell range A2:A14 in the “Sheet 1” tab in Google Sheets.Ĭell A1 is for the filed label “Name,” so we don’t consider that.How Do I Pick a Random Name and Make It Static in Google Sheets?.Formula to Pick a Random Name from a Long List in Google Sheets.Basic Formula Logic Random Number Offset. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |