# indirect formula ???

#### palaeontology

##### Active Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### Well-known Member
Hi Palaeontology,

You can do it with INDIRECT and also OFFSET

Here's my test data:

Book1
BCNOPQ
11
22
33
44
55
66
77
88
9914
1010
111138
1212
1313
1414
1515
This weeks' fixtures

Here are the formulae:

Book1
BCD
1INDIRECTOFFSET
21414
3
43333
Sheet1
Cell Formulas
RangeFormula
B2B2=INDIRECT("'This weeks'' fixtures'!"&"C"&'This weeks'' fixtures'!N9)
D2D2=OFFSET('This weeks'' fixtures'!\$C\$1,'This weeks'' fixtures'!N9-1,0)
B4B4=SUM(INDIRECT("'This weeks'' fixtures'!"&"C"&'This weeks'' fixtures'!N11&":C"&'This weeks'' fixtures'!P11))
D4D4=SUM(OFFSET('This weeks'' fixtures'!C1,'This weeks'' fixtures'!N11-1,,'This weeks'' fixtures'!P11-'This weeks'' fixtures'!N11+1))

#### palaeontology

##### Active Member
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

##### Well-known Member
Palaeontology, Are you a T. Rex?

#### palaeontology

##### Active Member
lol, re the small-brained T-Rex.

Replies
0
Views
108
Replies
1
Views
82
Replies
3
Views
78
Replies
2
Views
163
Replies
5
Views
82

1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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