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

Answer 2

To utilise the results of a list in LibreOffice calc as the definition for a secondary list the following works:

First lists defined in DATA>DEFINE RANGE e.g. "Breakfast", "Dinner" where each item is a list containing a number of lines and is collectively named as either "Breakfast" or "Dinner".
detailed menu choices

the definition of the list named "Breakfast" might include: Bacon, Beans, Eggs, Hash Brown, Mushrooms, etc... this list is best defined on a separate sheet.

If you want to select the menu choices from each list based on the selection of either "Breakfast" or "Dinner" you need a list defined in data>validity with those two options. Say this is defined in cell A2.
Top level choices

To connect the two detailed menu choices named either "Breakfast" or "Dinner" with the choice between either, use the formula:

=indirect(A2)

in say cell C2.

When you select a choice from your defined list in A2, the spreadsheet will now use the entry in cell A2 to provide the correct list of choices in cell C2.

I hope that makes sense - and a big thanks to Waldir Leoncio for the reminder!