Function Problems

Automatically Fill the Next Reviewer from an Excel List

John WilsonJohn Wilson Nov 14, 2026 869 views

Question details

Automatically select the next reviewer from a list that can contain blanks and change over time.

Automatically Fill the Next Reviewer from an Excel List
Product
Microsoft Excel
Device & OS
not provided
Scenario
A reviewer list is maintained in column B, and the next reviewer should follow the previous reviewer in column F.
Observed behavior
The desired result skips blank entries, follows the current list order, wraps to the first reviewer, and handles a previous reviewer who is no longer listed.
Before you start

Place reviewer names in B2:B100 and the previous reviewer's name in F2. Reserve D1:D100 for helper values, and ensure reviewer names are unique and spelled consistently.

Solution 1Recommended

Use helper cells to rotate through the reviewer list

Use this formula-based approach to select the next nonblank name in the current list order.

The helper column numbers nonblank reviewer names. The result formula looks up the previous reviewer, selects the next numbered name, and returns the first name if it reaches the end or the previous reviewer is missing.

Formulas recalculate when the list changes. If completed assignments must remain unchanged, copy their cells and use Paste Special > Values before changing the list.

1
Start the helper column

Enter 0 in D1. In D2, enter =IF(B2="","",MAX($D$1:D1)+1), then drag the fill handle down through D100.

2
Enter the previous reviewer

Type the previous reviewer's name in F2. Leave F2 blank if you want to start with the first name in the list.

3
Calculate the next reviewer

In F3, enter =IF(MAX($D$2:$D$100)=0,"",IFERROR(INDEX($B$2:$B$100,MATCH(IFERROR(INDEX($D$2:$D$100,MATCH(F2,$B$2:$B$100,0)),0)+1,$D$2:$D$100,0)),INDEX($B$2:$B$100,MATCH(1,$D$2:$D$100,0)))).

4
Extend the rotation

Drag the fill handle from F3 down column F to generate subsequent reviewers. Add, remove, or reorder names within B2:B100 as needed.

Use helper cells to rotate through the reviewer list
List range and blank results: The result is blank when B2:B100 has no names. To include names beyond row 100, extend both the helper formulas and every corresponding B and D range in the result formula.
Reviewer rotation in WPS Spreadsheets

Rotate reviewers automatically in WPS Spreadsheets

Use a helper column and formulas in WPS Spreadsheets to select the next reviewer, skip blank list entries, and wrap to the first name.

  1. 1. Set up the list: Open the workbook in WPS Spreadsheets, enter reviewer names in B2:B100, and enter the previous reviewer in F2.
  2. 2. Number the names: Enter 0 in D1. Enter =IF(B2="","",MAX($D$1:D1)+1) in D2, then drag the fill handle through D100.
  3. 3. Find the next reviewer: Enter =IF(MAX($D$2:$D$100)=0,"",IFERROR(INDEX($B$2:$B$100,MATCH(IFERROR(INDEX($D$2:$D$100,MATCH(F2,$B$2:$B$100,0)),0)+1,$D$2:$D$100,0)),INDEX($B$2:$B$100,MATCH(1,$D$2:$D$100,0)))) in F3.
  4. 4. Continue and save: Drag the fill handle from F3 down column F for more assignments, then use File > Save As to save the workbook in XLSX format.
Automatically rotate through a changing reviewer listOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill handlesWork with spreadsheets in a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens when the previous reviewer is the last name in the list?

The formula in F3 wraps to the first nonblank reviewer in B2:B100.

What if the previous reviewer has been removed?

If the name in F2 is not found in B2:B100, the formula starts again with the first nonblank name.

Will blank cells interrupt the rotation?

No. The helper formulas in D2:D100 number only nonblank cells in column B, so blank entries are skipped.

Why did earlier assignments change after I reordered the list?

Formula results recalculate using the current list order. To preserve completed assignments, copy their cells and use Paste Special > Values before changing the list.

Can two reviewers have the same name?

The lookup matches the first occurrence of a name in B2:B100. Use unique names or identifiers to distinguish reviewers.