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

Question

Say I have Libreoffice.org Calc (perhaps this applies to MS Excel), which is defined as the range $Sheet1.$A$1:$A$4 .

I also declared a constant with a value of 1. For this layout purpose, call it startingLine .

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

What I would like to do is turn strings of a given range into variables. In my opinion, all that is needed is to define it like this: $Sheet1.$A$startingLine:$A$4 , but this will not work .: - /

I am looking for a simple solution for spreadsheets, unfortunately macros will not do this for me this time. However, a solution using the R1C1 link would be pretty good. :)

Any help would be greatly appreciated!

Answer 1

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

To determine the range for you, there may be =INDIRECT(CONCATENATE("$sheet1.$A$",startingLine,":$A$4"))
for example, to sum (in excel) =SUM((INDIRECT(CONCATENATE("$A$",startingLine,":$A$4")))) , but I'm not familiar with the defining ranges in libreoffice

Answer 2

To use the list results in LibreOffice calc as a definition for a secondary list, follow these steps:

The first lists defined in DATA>DEFINE RANGE , for example. “Breakfast”, “Dinner”, where each item is a list containing a series of lines and referred to as “Breakfast” or “Dinner”.
detailed menu options

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

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

To connect two detailed menus called “Breakfast” or “Dinner” with a choice between them, use the formula:

 =indirect(A2) 

in cell C2.

When you select a selection from your specific list in A2, the spreadsheet will now use the entry in cell A2 to provide the correct list of options in cell C2.

Hope this makes sense - and many thanks to Valdir Leoncio for the reminder!