One way:

Create a defined name (Insert/Name/Define), say

Name in workbook: MyVariableRange
Refers to: =INDIRECT(Summary!$A$1 & "!A1:Z1000")

Then in your worksheet, enter

A1: =INDEX(MyVariableRange,ROW(),COLUMN())

Drag to A1:Z1000.


In article ,
Eric wrote:

How to modify "!J10" into a variable?
If I drag and drop this cell, all cell locations will refer to J10 only.
I would like to drag and drop this cell across a table A1:Z1000, therefore,
In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1")
In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12")