Solved which formula to use in from of sheet1...in Excel

November 14, 2017 at 19:32:05
Specs: Windows 8
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

See More: which formula to use in from of sheet1...in Excel

Reply ↓  Report •

#1
November 14, 2017 at 20:12:57
✔ Best Answer
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


Reply ↓  Report •
Related Solutions


Ask Question