CODEWITHSUNDEEP
excelexcel-formula
Jervis Chionh
Jervis Chionh

Reputation: 127

INDIRECT with increasing cell reference A1 A2 A3 etc

may i know how to enable (CELL A1 in SHEET1)

=INDIRECT('SHEET2.A1')

to drag through columns and rows accordingly without having to type it out one by one

Upvotes: 6

Views: 45746

Answers (4)

barry houdini
barry houdini

Reputation: 46361

Sometimes CELL function can be useful here, e.g.

=INDIRECT("'Sheet1'!"&CELL("address",A1))

That can be dragged down or across

Upvotes: 14

brettdj
brettdj

Reputation: 55682

On second thought, posting my comment to another answer as an answer

For copying down

You can use
=INDIRECT("'Sheet1'!A"&ROW(A1))
to increment directly without a helper column

Note that the ' is needed around the sheet name to handle situations where the sheet you are referencing is 'Sheet 1' rather than Sheet1

For copying across
=INDIRECT("'Sheet1'!A"&COLUMN()-1)

In Cell B1 will give = Sheet1A1
In Cell C1 will give = Sheet1A2
etc

Upvotes: 3

SeanC
SeanC

Reputation: 15923

an option would be to try OFFSET.

OFFSET(reference, rows, cols, [height], [width])

link to: microsoft help

perhaps using ROWS(), or even work it out based on values - I've used it to look up info based on formula like MONTH(Q11)

Upvotes: 0

James L.
James L.

Reputation: 9461

You can use a cell reference in the Indirect() function, which resolves to the value in the referenced cell. Given Sheet1:

ColA
------
This
is
a
test

And these values and formulas in Sheet2:

ColA    ColB
------  ------
A1      =INDIRECT("Sheet1!"&A1)
A2      =INDIRECT("Sheet1!"&A2)
A3      =INDIRECT("Sheet1!"&A3)
A4      =INDIRECT("Sheet1!"&A4)

The values in Sheet2 look like this:

ColA    ColB
------  ------
A1      This
A2      is
A3      a
A4      test

It is easy to then fill-down (drag fomulas down) that auto-increment the cell references in column A, and the indirect formulas in column B.

Upvotes: 3

Related Questions