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