# INDIRECT with variable cell value.

#### KWilkinson

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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:
Code:
``````[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.

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

You are welcome!
Glad I was able to help!

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...

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)

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

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

Replies
11
Views
365
Replies
7
Views
356
Replies
2
Views
177
Replies
3
Views
169
Replies
2
Views
208

1,196,359
Messages
6,014,804
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back