INDIRECT with variable cell value.

[KWilkinson]

Hi all,

I have the following formula in a spreadsheet : =INDIRECT("'"&$F$12&"'!"&"O3")

This as expected returns the value in cell O3 of the sheet name defined in cell F12. So far so good.

However I want to be able to drag this formula down and each time have it reference the next cell down, i.e. O3, O4, O5 etc. Because the formula treats the O3 as a string (I think anyway) it doesn't change. So my question is is there a way to do this so that as the formula is copied down the target cell increases?

Many thanks in advance for any suggestions on this.

Cheers

K.

 

[Joe4]

Welcome to the Board!

Let's say that you are putting this formula in row 1 and copying down. Then we can use the ROW() function, which when left blank, simply returns the row number the formula is place in.
So, to get the formula in row 1 to return O3, we would do this:

[COLOR=#333333]=INDIRECT("'" & $F$12 & "'!O" & [/COLOR][COLOR=#ff0000][B]ROW()+2[/B][/COLOR][COLOR=#333333])
[/COLOR]

So, as you copy down to rows 2,3,4 that value increases along with it, doing what you want.

So that +2 is just the difference between what row the formula is placed in, and what number you need returned (3-1=2). Adjust accordingly.

 

[KWilkinson]

Many thanks! That seems to do what I need very nicely.

 

[Joe4]

You are welcome!
Glad I was able to help!

 

[KWilkinson]

I thought it would be easy to change this to increment Columns and Rows using the same approach but I just can't seem to get it to work. The basic formula I'm trying is as follows;

=INDIRECT(COLUMN()+1&ROW()+1)

I'm expecting it to return the value from the cell 1 across and 1 down but all I get is a REF error...

 

[KWilkinson]

I understand the problem now. COLUMN() will return a number not a letter so the format R1C1 needs to be used. The basic formula I'veused is below.

=INDIRECT("R"&ROW()+1&"C"&COLUMN()+1,FALSE)

 

[Joe4]

So, are you all set now, or are you still having problems?

 

[KWilkinson]

I think I'm good - I have it working using the approach above just not 100% sure it's the neatest solution.