Using indirect, vlookup, or index match on named ranges does not reference the correct data when applied on another sheet within the workbook.
For example, you have Sheet1 and Sheet2 on a workbook. You want to use a named range (ie. “Sheet1!$F$6”) 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 it is on Sheet1. However, what you get is only one period displayed multiple times on Sheet2.
This is because the named range is a locked reference on cell F6. 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!$F$6:$F$7”
Attached is a sample report off of the Data Warehouse Corporate Demo Database that uses an Excel Indirect on a named range.
Applies to: Report Designer 4.x and 5.x