Get cell range for a function argument from the text of another cell in libreoffice-calc

Question

Consider some libreoffice-calc function called MYFUNC which takes a cell range for example A2:B40 as an argument, so I can call MYFUNC(A2:B40).

Now I want to be able to write A2:B40 as text into another cell say D4 and give the cell range defined in D4 as an argument to MYFUNC. I.e. I want to be able to write something like this MYFUNC(GETCELLRANGEFROM(D4) which should be equivalent to MYFUNC(A2:B40).

Answer 1

Replacing GETCELLRANGEFROM(D4) by INDIRECT(D4) should do what you want to achieve (given that D4 contains the string A2:B40). The INDIRECT function "translates" a string into a reference.

Here's a short example how it works:

• A1 to A9 holds some numbers,
• B1 has a string value, representating a reference to column A;
• C1 has the formula =SUM(INDIRECT(B1))

So, C1 calculates a sum, based on the reference string in B1. Modifying B1 to A1:A7 will change C1, too (resulting in 28). This way, INDIRECT "translates" a string to a reference that can be used in another (surrounding) formula.