Function Problems

How to Use a Dynamic Worksheet Name in an Excel Formula

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to set up formulas that automatically reference a different worksheet based on the current month, eliminating the need to manually update sheet references.

Product: Excel
Device & OS: not provided
Scenario: Managing monthly data across multiple worksheets and consolidating it dynamically into a primary Utility sheet.
Observed behavior: The formula needs to dynamically update to the current month's worksheet name instead of using a static, hardcoded sheet reference.

Before you start

Ensure that your monthly worksheets are named exactly as the full month names (e.g., 'January', 'February') so they perfectly match the dynamic text generated by the formula.

Solution 1Recommended

Use INDIRECT and Named Ranges for Dynamic Sheet References

Combine the INDIRECT and TEXT functions to build a dynamic text string that automatically points to the current month's worksheet.

The INDIRECT function evaluates a text string as a cell reference. By combining it with the TODAY and TEXT functions, you can generate a string that perfectly matches the current month's sheet name.

Define a dynamic name

Go to the 'Formulas' tab, click 'Name Manager', and select 'New'. Enter the name 'ThisMonth' and type '=TEXT(TODAY(),"mmmm")' in the 'Refers to' field.

Build the INDIRECT formula

In your Utility sheet where you want the data to appear, type your formula using INDIRECT. For example, to reference cell B2, enter '=INDIRECT(ThisMonth&"!B2")'.

Calculate the result

Press Enter. The formula will automatically pull the value from cell B2 of the active month's worksheet.

Syntax Tip: If your worksheet names contain spaces, you must include single quotes around the sheet name reference, like this: =INDIRECT("'" & ThisMonth & "'!B2").

Work Smarter with WPS Spreadsheet

Dynamically Reference Worksheets in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDIRECT, TEXT, and TODAY, allowing you to seamlessly create dynamic worksheet references for monthly data consolidation. It is lightweight, completely free, and ensures your complex data models work flawlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your monthly worksheets.
2. Create a Name Manager rule: Navigate to the 'Formulas' tab and click on 'Name Manager' to define the 'ThisMonth' variable using the TEXT and TODAY functions.
3. Apply the dynamic formula: Use the =INDIRECT function in your target cell to link the defined name to your desired cell reference.
4. Save your work: Press Enter to execute the formula and save your document in the universally compatible .xlsx format.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Supports advanced dynamic referencing like INDIRECT and Name Manager.Lightweight, fast, and highly intuitive for complex data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a cell value instead of the current month for the dynamic sheet name?

Yes, you can reference a specific cell containing the target sheet name. For example, if cell A1 contains the sheet name 'Sales', you can use the formula =INDIRECT("'" & A1 & "'!B2") to pull data from cell B2 of the Sales sheet.

Why is my INDIRECT formula returning a #REF! error?

A #REF! error usually occurs if the referenced worksheet does not exist, the sheet name is misspelled, or if the text string generated by the formula doesn't exactly match the tab name. It can also happen if you are referencing a closed external workbook.

Does the INDIRECT function work across different workbooks?

Yes, INDIRECT can reference cells in a different workbook. However, the referenced workbook must be open. If you close the external workbook, the INDIRECT function will fail and result in a #REF! error.

Excel & Data Problems