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

Do you think Europe's new data protection laws will affect you?

Discuss in The Lounge

Poll History