indirect formula ???

[palaeontology]

I've always struggled getting my head around indirect, to the point where I'm, not really sure if this is the function I need to use in this particular instance ...

In my current worksheet, I'd like to return the contents of a cell from a different worksheet (same workbook though) ... however the cell I need to refer to can change from week to week.

For example the formula this week might be ... ='This weeks'' fixtures'!C13 but next week might be .... 'This weeks'' fixtures'!C29

so, in my current sheet I have a designated cell (cell N9) which the user is meant to enter the row number that's relevant for that particular week

So, if the user types 18 into cell N9, I need the formula to refer to the following cell .... 'This weeks'' fixtures'!C18

How do I make the formula dynamic like that ?

and on the same worksheet, I need a formula to refer to a cell range (not just a single cell) which will also be changing from week to week.

So I've got two other designated cells the user would need to enter certain row values into (cells N11 and P11), so, if the user types 34 into cell N11 and 48 into cell P11, I need the formula to refer to the following cell range .... 'This weeks'' fixtures'!C34:C48

Is anyone able to help me ?

Kind regards,

Chris

 

[Toadstool]

Hi Palaeontology,

You can do it with INDIRECT and also OFFSET

Here's my test data:

Book1
	B	C											N	O	P	Q
1		1
2		2
3		3
4		4
5		5
6		6
7		7
8		8
9		9											14
10		10
11		11											3		8
12		12
13		13
14		14
15		15
This weeks' fixtures

Here are the formulae:

Book1
	B	C	D
1	INDIRECT		OFFSET
2	14		14
3
4	33		33
Sheet1

Cell Formulas
Range	Formula
B2	B2	=INDIRECT("'This weeks'' fixtures'!"&"C"&'This weeks'' fixtures'!N9)
D2	D2	=OFFSET('This weeks'' fixtures'!$C$1,'This weeks'' fixtures'!N9-1,0)
B4	B4	=SUM(INDIRECT("'This weeks'' fixtures'!"&"C"&'This weeks'' fixtures'!N11&":C"&'This weeks'' fixtures'!P11))
D4	D4	=SUM(OFFSET('This weeks'' fixtures'!C1,'This weeks'' fixtures'!N11-1,,'This weeks'' fixtures'!P11-'This weeks'' fixtures'!N11+1))

 

[palaeontology]

Toadstool, thankyou very much for that.

I think I have a handle on indirect now, but will need to get my head around offset.

Small-brain syndrome is a killer.

Ta again,

Chris

 

[Toadstool]

Palaeontology, Are you a T. Rex?

 

[palaeontology]

lol, re the small-brained T-Rex.