# help with INDIRECT

#### thesproing

##### Board Regular
HI guys,

What i would like to do is for a particular cell to display a value form another sheet depending on the value of a cell in the same sheet.

Im kinda new to excell, and not too confident with the forulae but im gonna write this out mathamatically and perhaps you can help.

can you write this in excell?

IF B2 = x THEN = ('x-1"-"x')!O3

where B2 can be 2005, 2006, 2007 etc, and the worksheets in my book are called 2005-2006, 2007-2008 etc...

I think im very close with =INDIRECT("'"&B2-1&"-"&B2&"'!O3")
except that if i copy that down or accross the same value is displayed in every cell, ie O3,

any ideas?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### njimack

##### Well-known Member
If I understand your example, the following should work..

=INDIRECT(\$B\$2-1&"-"&\$B\$2&"!O3")

EDIT: Changed relatives to absolutes, per Thorin's post.

#### Thorin

##### Board Regular
If you wish your formula to ALWAYS reference cell B2, then you will have to make it an absolute reference.

Replace B2 with \$B\$2 in each case, and when you copy it down or across it will still reference cell B2.

#### thesproing

##### Board Regular
yes, i do want it to always reference B2, but the problem is that it ALWAYS references O3,

Thaks for the above, but sadly INDIRECT(B2-1&"-"&B2&"!O3") doesnt seem to work, it just gives me a REF error,

The above formulae, in my previous post works perfectly, except that O3 remains constant if i copy the formulae down/ accross.

Thanks

#### njimack

##### Well-known Member
The above formulae, in my previous post works perfectly, except that O3 remains constant if i copy the formulae down/ accross.

In that case, try using ROW()...

=INDIRECT("'"&B2-1&"-"&B2&"'!O"&ROW())
(this assumes your formulae start in Row 3)

#### Thorin

##### Board Regular
Try :-

Code:
``=INDIRECT("'"&\$B\$2-1&"-"&\$B\$2&"'!"&ADDRESS(ROW(),COLUMN(O1),4))``

Again this assumes that your formula starts in Row 3.

#### thesproing

##### Board Regular
perfect, although it will still only let me fill down, but thats a lot easier than writing it out seperately for each cell!

But, in theory there must be a way to make the colum reference variable as well. Any ideas?

#### Thorin

##### Board Regular
Did you try my last post ? Your post and mine may have crossed over.

#### thesproing

##### Board Regular
perfect, thanks a lot guys, this has really helped

Replies
2
Views
248
Replies
11
Views
821
Replies
4
Views
259
Replies
4
Views
241
Replies
0
Views
357

1,181,649
Messages
5,931,214
Members
436,784
Latest member
amuljono

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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