How to define dynamic ranges on Calc (or Excel)?

Question

Let's say I have a Libreoffice.org Calc (maybe this goes for MS Excel too) object defined as the range $Sheet1.$A$1:$A$4.

I also have declared a constant with value 1. For this mockup purpose, let's call it startingLine.

Both objects are properly defined in the Define Names dialog (shortcut: Ctrl+F3).

What I would like to do is to turn the lines of the defined range into variables. In my mind, all it'd take would be to define it like this: $Sheet1.$A$startingLine:$A$4, but this doesn't work. :-/

I'm looking for a simple spreadsheet solution, unfortunately macros won't do it for me this time. A solution using R1C1 reference would be good enough, though. :)

Any help will be greatly appreciated!

Answer 1

You will need to use a version of this formula (excel) =CELL("contents",INDIRECT(CONCATENATE("A",startingLine)))
This gives you the contents of cell A1 where startingLine is a constant 1. Additionally, if we define endingLine as A, we can replace A in the formula:
=CELL("contents",INDIRECT(CONCATENATE(endingLine,startingLine)))

To define a range for you might be =INDIRECT(CONCATENATE("$sheet1.$A$",startingLine,":$A$4"))
for instance to sum (in excel) =SUM((INDIRECT(CONCATENATE("$A$",startingLine,":$A$4")))) but I am unfamiliar with the defining ranges in libreoffice