Function Problems

Dynamically Reference an Excel Table Based on a Cell Value in WPS Spreadsheets

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 869 views

Question details

The user needs an Excel formula to dynamically reference tables named Rota14, Rota15, or similar based on the number stored in cell CP18.

Dynamically Reference an Excel Table Based on a Cell Value
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
The workbook contains multiple identically structured tables with the Rota prefix, and one formula must switch between them based on the suffix value in CP18.
Observed behavior
The current formula uses a fixed table name and does not update when CP18 changes, or the user needs to build the dynamic reference rather than edit the formula manually.
Before you start

Confirm that the workbook contains tables named exactly Rota14, Rota15, or the matching Rota suffix pattern, and that CP18 contains the numeric suffix you want to use. Also verify that each table has the same MON through SAT columns used in the structured reference.

Solution 1Recommended

Use INDIRECT to Build the Dynamic Structured Table Reference

Use this solution when you need one formula to switch between tables such as Rota14 and Rota15 based on the value in cell CP18.

INDIRECT converts a text string into a real cell or table reference. It is volatile and may slow large workbooks, so use it for targeted formulas rather than filling thousands of cells.

1
Select the formula cell

Click the cell that currently contains the formula with a hard-coded table reference such as Rota14 or Rota15.

2
Locate the table reference to replace

In the formula bar, find the data range reference such as Rota14[[MON]:[SAT]] and the header reference such as Rota14[[#Headers],[MON]:[SAT]].

3
Replace with INDIRECT references

Use INDIRECT("Rota"&$CP$18&"[[MON]:[SAT]]") for the data range, and INDIRECT("Rota"&$CP$18&"[[#Headers],[MON]:[SAT]]") for the header row. The quoted text is combined with the CP18 value using &.

4
Apply the full formula and verify

For example: =IF(INDEX(INDIRECT("Rota"&$CP$18&"[[MON]:[SAT]]"),MATCH(CO16,date[W/C Date],0),MATCH(CR17,INDIRECT("Rota"&$CP$18&"[[#Headers],[MON]:[SAT]]"),0))="off","7.5",""). Press Enter, then change CP18 to 14 or 15 and confirm the correct table is referenced.

Volatile function warning: INDIRECT recalculates whenever any cell in the workbook changes. In very large workbooks, limit its use to avoid performance issues.
WPS Spreadsheets formula

Create Dynamic Excel Table References with WPS Spreadsheets

Use INDIRECT with structured references in WPS Office to switch between Excel tables based on a cell value, so one formula can return data from Rota14, Rota15, or any matching table without rewriting the reference.

  1. 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the Rota14/Rota15 tables and the formula cell.
  2. 2. Select the formula cell: Click the cell that needs the dynamic table reference and open the formula bar.
  3. 3. Enter the INDIRECT dynamic reference: Replace the fixed table name with INDIRECT("Rota"&$CP$18&"[[MON]:[SAT]]") and use INDIRECT("Rota"&$CP$18&"[[#Headers],[MON]:[SAT]]") for the header reference.
  4. 4. Test the formula with CP18: Change cell CP18 to 14 or 15 and press Enter. Verify that the formula returns the expected value from Rota14 or Rota15.
Switch between multiple Excel tables without editing the formulaUses Excel-compatible INDIRECT and structured reference syntaxOpens and saves XLSX workbooks with full table and formula supportFree, lightweight Office suite with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT work for dynamic table names?

INDIRECT converts a text string into an actual reference. By concatenating "Rota" with the value in CP18, you create a text string like "Rota15", and INDIRECT evaluates it as the Rota15 table reference.

Can I use INDIRECT with structured table headers?

Yes. Include the structured reference portion as text, for example INDIRECT("Rota"&$CP$18&"[[#Headers],[MON]:[SAT]]"). This lets the header row also change dynamically with CP18.

What should I do if the formula returns #REF!?

Check that the table name exactly matches the generated text, such as Rota14 or Rota15, and that CP18 contains a matching suffix. Also confirm the table includes the MON through SAT columns used in the structured reference.

Will INDIRECT slow down my workbook?

INDIRECT is volatile, meaning it recalculates whenever any cell changes. It is fine for a few formulas but should be limited in very large workbooks to avoid performance problems.

Does this dynamic table reference work in Microsoft Excel and WPS Spreadsheets?

Yes. The INDIRECT and structured reference syntax is compatible with Excel workbooks, so the same formula approach works in both Microsoft Excel and WPS Spreadsheets.