Excel – How to keep random numbers from changing in Excel

I'm going to put the randomization in the formula itself. You could always put this in another column if you want.

Column A is name
Column B is office
Column C is year

Cell G2 is the office you are interested in
Cell G3 is the year you are interested in

=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), RANDBETWEEN(1,COUNTIFS(B:B,G2,C:C,G3))))

This is an array formula and needs to be entered using Ctrl+Shift+Enter.

It works by creating an array that has the row numbers (if criteria is matched) or zero if it doesn't. Then it takes the nth largest value, where n is a random number between 1 and the number of rows that match the criteria.

Charles Williams has a good explanation of how Excel calculates things and why.

http://www.decisionmodels.com/calcsecretsi.htm

In essence, if a workbook contains volatile functions (refer to Charles' list, since what's a volatile function has changed over the years with different Excel versions), a whole workbook recalculation will be triggered when any cell in the workbook is changed (if Excel is set to automatic calculation). If there are no volatile functions in the workbook, only the cells that are affected by the last change will recalculate.

Others here have stated that automatic calculation will always calculate everything in the workbook when any cell is changed, but that is wrong. Only volatile functions cause the automatic recalculation of all cells in the workbook. Without volatile functions, Excel recalculates only what needs to be recalculated.

That is why people like me, who know the difference, strongly advise to avoid volatile functions like OFFSET() or INDIRECT() in worksheet cells if possible, since whey will slow the workbook down by causing a full recalculation after every cell change. Learning to use INDEX() instead of OFFSET() can result in dramatic speed improvements, because Index is not volatile (see Charles' article for more details).

Excel's calculation engine is based on the "recalc or die" philosophy that set Excel apart from competing products when it was in development. Check out this article: https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the-odds/