Dynamic cell reference in formula that points to another tab

[atf32]

I have a simple formula in cell A1 that points to a cell on a different sheet ("=Sheet3!J4"). However, need to change this to make is more dynamic. For instance, I need the actual cell reference part (J4) to be more dynamic. So in some cases I may want to pass a different row. How can I write this formula so that I can pass a different row in it, to pick the data in a different row?

 

[Joe4]

You can use the INDIRECT function to dynamically build your range reference.
Let's say that you are putting the row number in cell A1. Then you could write your formula like this:

=INDIRECT("Sheet3!J" & A1)

 

[Johnny C]

The simplest way is to use the OFFSET function.
If in A2 you have the number of rows you may want to move, use the formula in A1:
=OFFSET(Sheet3!J4,A2,0)
By default it will be 0. If you want J6, enter 2 in A2 and it will return that value.

Have a google on help for OFFSET. It's a very powerful function, a lot of people aren't happy about it for (IMHO) overly technical reasons. But I use it all the time, and have done for 15+ years

 

[Johnny C]

Another way is to use a 'Dynamic name'. Press ctrl-F3 to bring the name manager up.

Give it a name, MyCell for e.g. Then in the refers to box, put "=OFFSET(Sheet3!J4,Sheet1!A2,0)"

Then in any sheet, you can type =MyCell and it will bring the value from Sheet3 based on what is in Sheet1.

Where this becomes very useful, say you wanted to always bring the last item in a vertical list. Say Sheet3 colj J could have any number of items below J4 (with no blanks, that's important), and you always wanted the last 1.

In the Name Manager call the name 'LastItem' and in the refers to box you would enter "=OFFSET(Sheet3!J4,COUNTA(Sheet3!J4:J1000000),0)"
Then on any sheet if you wanted the last item you would type =LastItem.
and as long as there were no blanks and the last item wasn't below row 1,000,000 it would bring back the last item. If you wanted the last but 1 item, the refers to would contain
"=OFFSET(Sheet3!J4,COUNTA(Sheet3!J4:J1000000)-1,0)"
And so on, there's a world of possibilities with 'dynamic range names'.

 

[atf32]

This is exactly what I was looking for. Earlier, I wasn't using the Indirect correctly. Thanks for the help and the quick response.

 

[Joe4]

You are welcome!