Using a cell value for row in R1C1

[Rob T]

Hi everyone,

Maybe a slightly bizarre question and I don't know if it's possible or
not.

What I'd like to do is use a value in a cell to be used in as the row
value in an R1C1 function.

e.g.

Column A goes from rows 1 - 47 so I put 47 in a cell and name the cell
"BotRow".

I then have a formula which would be something like:

=sum(R1C1:R"BotRow"C1)

Is there any way of doing this

The actual use of this is so that where I have a whole grid of formulas
and want to change the range they're looking at, I don't have to change
all the formulas, just a value in one cell.

Any help appreciated,

Rob

 

[Ron Coderre]

Here are some options:

If using R1C1 style:
=SUM(OFFSET(R1C1,0,0,BotRow,1))

If using A1 style:
=SUM(OFFSET(A1,0,0,BotRow,1))

If using either style:
=SUM(INDIRECT("R1C1:R"&BotRow&"C1",0))

Do any of those help?

Regards,
Ron

 

[Rob T]

Excellent, that first one was just the ticket!

Thanks for the help.

Rob