formula :Referencing a cell based on the value in another cell

[Crazy Badger]

I have a simple formula (=Sheet1!G3) to pull the contents of a cell from other worksheet into my current worksheet.

I want to copy this down 100 lines and the data is always found in column G but the row value changes (i.e. next row might be =Sheet1!G26)

Rather than editing each formula (as I have this down multiple columns) I wanted to add the row reference into the 1st column of my current worksheet and created a formula a little like this

=Sheet1!G(A1) where A1 = the row number from the worksheet row I am looking up (i.e. 3)

Hope this make sense and someone know an answer!

Thanks - Adrian

[Mark@Work]

The formulas you are looking for are INDIRECT() possibly in conjuction with ADDRESS()
=INDIRECT(ADDRESS(A1,7,,,"Sheet1"))

or possibly you could use OFFSET()
=OFFSET(Sheet1!$G$1,A1,0)

Mark.

[Mark@Work]

A third option might be to use INDEX()
=INDEX(Sheet1!G:G,A1)

Which you use is largely a matter of personal taste...

[Crazy Badger]

Thanks Mark

Got my head around =INDIRECT and although it probably too longer to sort this time (research time!) it will save me days in the future!