Ex:(Excel) I need to copy data from other sheet like =sheet1!$A$2 and I need to copy more down and need to show change only sheet number by order like

a) ='sheet1!$A$2

b) ='sheet2!$A$2

c) ='sheet3!$A$2

You can do this with the INDIRECT and ROW functions. The INDIRECT function turns a text string into a formula, assuming the syntax is correct. Since a text string is enclosed in quotes, these 2 functions will return the same result:

=Sheet1!$A$2

=INDIRECT("Sheet1!$A$2")

The ROW function returns the number of the Row in which the function is used.In Row 1, =ROW() will return 1

In Row 2, =ROW() will return 2

etc.In Row 4, this will return 3:

=ROW()-1

Therefore, if you put this in Row 1, it will reference Sheet1:=INDIRECT("Sheet"&ROW()&"!$A$2")

If you want to put the formula in Row 15, simply subtract 14 from the ROW() function to reference Sheet1:

=INDIRECT("Sheet"&ROW()-14&"!$A$2")

If you drag it down, it will increment the Row value each time, thereby incrementing the Sheet number.

Pop Quiz:

What do you think =COLUMN() returns?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Ask Your Question

Weekly Poll