Function Problems

Increment Worksheet Names While Keeping a Cell Reference Fixed in Excel

Huda QurayshiHuda Qurayshi Nov 18, 2026 870 views

Question details

Create a formula that increments worksheet names when filled down while always referencing cell H3.

Increment Worksheet Names While Keeping a Cell Reference Fixed in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has worksheets named Day(28), Day(29), Day(30), and so on, and a summary sheet needs to pull H3 from each one.
Observed behavior
Each filled formula should reference H3 on the next numbered Day worksheet.
Before you start

Check that the worksheet names follow the exact pattern Day(28), Day(29), and so on. Identify the row where the first formula will be entered so you can calculate the correct offset.

Solution 1Recommended

Use INDIRECT and ROW to increment worksheet names

Use this formula to retrieve H3 from consecutive Day worksheets as you fill down.

INDIRECT converts a text string into a worksheet reference. ROW supplies the changing number, while H3 stays fixed as part of the text.

1
Select the starting cell

On the summary worksheet, select B2 or another cell in row 2.

2
Enter the formula

Type =INDIRECT("'Day("&ROW()+26&")'!H3") and press Enter. In row 2, ROW()+26 produces 28, so the formula references 'Day(28)'!H3.

3
Fill the formula down

Drag the fill handle at the bottom-right corner of the cell down. The next rows reference H3 on Day(29), Day(30), and subsequent worksheets.

4
Adjust the offset if needed

If the formula starts in a different row or with a different Day number, replace 26 with the starting Day number minus the starting row number.

Use INDIRECT and ROW to increment worksheet names
Worksheet names must match: If a referenced worksheet does not exist or its name differs from the generated name, INDIRECT returns a #REF! error.
WPS Spreadsheets

Increment Day worksheet references in WPS Spreadsheets

Use INDIRECT and ROW in WPS Spreadsheets to pull the same cell from consecutively numbered worksheets, then fill the formula down.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the Day worksheets.
  2. 2. Select the starting cell: On the summary worksheet, select B2 or another cell in row 2.
  3. 3. Enter the formula: Type =INDIRECT("'Day("&ROW()+26&")'!H3") and press Enter to reference H3 on Day(28).
  4. 4. Fill down: Drag the selected cell's fill handle down to reference H3 on the following Day worksheets.
Increment worksheet names while keeping H3 fixedOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface and fill handleAccess spreadsheet tools in a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I start with Day(1) instead of Day(28)?

Subtract the starting row number from 1 to get the offset. For a formula in row 2, enter =INDIRECT("'Day("&ROW()-1&")'!H3").

Why does the formula return #REF!?

Check that the generated worksheet name exists and matches the tab name exactly. For example, the row 2 formula with a +26 offset requires a worksheet named Day(28).

Can I reference a cell other than H3?

Yes. Replace H3 inside the formula text with the cell you need, such as D5. The same cell will be referenced on each numbered worksheet.

Why doesn't H3 change when I fill the formula down?

H3 is written inside the text supplied to INDIRECT, so filling down does not adjust it. Only the value returned by ROW changes.