Return the Next Consecutive Number from a Selected Worksheet in WPS Spreadsheets
Question details
The user needs to select a worksheet based on a category number in column B and return the next unused consecutive number from that worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages multiple worksheets named by category and wants to pull the next available sequential number from the category sheet selected in column B.
- Observed behavior
- The next unused number must be returned automatically based on the category number in column B.
Before writing the formula, confirm that each category number in column B exactly matches a worksheet name, and identify the column where existing assigned numbers are stored in every category worksheet.
Use an INDIRECT and MAX Formula to Return the Next Number
Use this formula when worksheet names match the category values in column B exactly and assigned numbers are listed in the same column across category sheets.
The formula builds a dynamic worksheet reference from the category number in column B, finds the highest existing number in that worksheet, and adds 1. If the category sheet is empty or contains no numeric values, IFERROR returns 1 as the first number.
In WPS Spreadsheets, open the workbook that contains the category column and the category worksheets. Note the column letter where assigned numbers are stored in each category sheet.
Select the cell where the next number should appear, then enter =IFERROR(MAX(INDIRECT("'"&$B2&"'!$A:$A"))+1,1). Replace $A:$A with the actual used-number column range.
Drag the fill handle down to apply the formula to the remaining rows that contain category numbers in column B.
Check that the values in column B are exactly the same as the worksheet names. If a name has a space or extra character, the INDIRECT formula cannot resolve the reference.
Use Worksheet_Change VBA Code for Automatic Entry
Use this method if your WPS Office edition supports macros and you want the next number to be inserted automatically whenever a category number changes.
Generate the Next Unused Number from a Selected Worksheet in WPS Spreadsheets
WPS Spreadsheets can directly complete this task by using a dynamic INDIRECT formula that reads the category worksheet name from column B, finds the highest existing number, and returns the next consecutive value.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook containing the category column and category worksheets, and switch to the destination worksheet.
- 2. Prepare the data layout: Ensure column B holds the category number and every category worksheet stores assigned numbers in the same column.
- 3. Enter the lookup formula: In the destination column, enter =IFERROR(MAX(INDIRECT("'"&$B2&"'!$A:$A"))+1,1) and adjust $A:$A to the actual used-number column.
- 4. Fill and verify results: Drag the formula down and spot-check several category sheets to confirm the returned number is the next unused value.
- 5. Save the workbook: Save the workbook in WPS Spreadsheets. The formula remains functional when the file is opened in Microsoft Excel.
Frequently Asked Questions
Can WPS Spreadsheets pull data from a worksheet name stored in a cell?
Yes. You can use INDIRECT with a cell reference, such as =INDIRECT("'"&B2&"'!A:A"). The cell value must exactly match the worksheet name.
What should I do if my category worksheet names contain spaces?
Wrap the sheet name in single quotes inside INDIRECT, for example =INDIRECT("'"&B2&"'!A:A"), so WPS Spreadsheets treats it as a valid sheet reference.
Does the MAX+1 formula fill gaps in the number sequence?
No. It returns the next number after the highest existing number. To fill the first available gap, use a helper column or an array formula that checks numbers from 1 upward.
Is VBA required to return the next consecutive number?
No. A formula-based INDIRECT and MAX approach works for many layouts. VBA is optional if you want the value inserted automatically when the category number changes.
Can I use this method in Microsoft Excel too?
Yes. The same INDIRECT and MAX approach works in Excel because WPS Spreadsheets uses the same .xlsx workbook format and familiar worksheet functions.
