indirect formula ???

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
Palaeontology, Are you a T. Rex?
;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,895
Messages
5,627,504
Members
416,250
Latest member
darius_rebelo

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