This article explains how to use native Excel INDIRECT, VLOOKUP, or INDEX/MATCH on expanding named ranges in another sheet within the workbook.
Using INDIRECT, VLOOKUP, or INDEX/MATCH on named ranges will not reference the correct data when columns/rows expand upon running the report if the named range that is being reference does not include the next column/row to account for the expansion.
For example, you have Sheet1 and Sheet2 on a workbook and you want to use a named range (e.g. “Sheet1!$E$7”) to reference a Period End attribute from Sheet1 on an expanding column in Sheet2. When you run the report, you expect to see Period End fields to be populated in Sheet2 the same way they are on Sheet1. However, what you see is only one period displayed multiple times on Sheet2.
This is because the named range is a locked reference on cell E7. Named ranges are locked by Excel design. To fix this issue, please drag the reference one more column to the right (to account for expanding). In this case, it would be “Sheet1!$E$7:$F$7”
Attached is a sample report off of the Data Warehouse Corporate Demo Database that uses native Excel INDIRECT on a named range.
Report Designer 4.x - 5.x