help with INDIRECT

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
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
Joined
Jun 17, 2005
Messages
7,772
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
Joined
Feb 8, 2007
Messages
243
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
Joined
Jul 16, 2007
Messages
76
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
Joined
Jun 17, 2005
Messages
7,772
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
Joined
Feb 8, 2007
Messages
243
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
Joined
Jul 16, 2007
Messages
76
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
Joined
Feb 8, 2007
Messages
243
Did you try my last post ? Your post and mine may have crossed over.
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top