Using formula to calculate cell reference

[nickthistleton]

Hi, it would really help if I could crack this one. Is it possible to use a formula to calculate a cell reference within a formula? e.g. is there some way I could reference cell C47 in a formula by saying C(40+A2) where A2 contains the value 7. I often want to sum a certain number of cells in a list, from the first one to the nth one and need to be able to calculate rather than hard-code n. Hope that makes sense and hope someone can help!

Thanks.

[TMS]

=INDIRECT("C" & (40 + A2))

Regards

[DonkeyOte]

I would advise use of INDEX

Please Login or Register  to view this content.

You can also use OFFSET

Please Login or Register  to view this content.

but like INDIRECT OFFSET is also Volatile (see link in sig. for more info on Volatility)

[nickthistleton]

Perfect. Many thanks indeed.

[deVIAous]

Sorry to be thick, but could someone please explain how the fields in the INDEX and OFFSET suggestions relate to the problem as originally posed. E.g. where does 'C' mean 'Column C' (so the row number of the cell still needs to be specified)' and where does it simply mean 'Column' (so that what follows is a computation of the column number) ? Where does 'C1' come from, and what does 'C:C' mean ?
As a septuagenarian, I found the descriptions of INDEX and OFFSET in the standard Excel online help quite impenetrable.

[FDibbins]

deVIAous, welcome to the forum, and congrats on being here

Normally we would ask for a new thread on your question, but seeing as how you are just asking for how the formulas work, we will let that slide

If you mean the C in this...
=INDIRECT("C" & (40 + A2))

INDIRECT() is excel's function for tranlating text into something that excel can use as a reference in a formula, so that translates to a cell reference in Column C that is in row 40 + whatever value is in A2. So if A2 contains 10, then =INDIRECT("C" & (40 + A2)) is the same as the reference =C50 (40+10)

OFFSET() is used to create a reference (or a range) that a specified number of rows and columns from a designated starting cell, and (for a range) is a specified number of rows high and number of columns wide...

=SUM(OFFSET(C1,,,40+A2,1))

this is creating a range (to sum), that has its start in C1, goes down zero rows and across zero columns (to determine the starting point), then creates a range that is 40+A2 rows deep and 1 column wide

Make sense?