Composing formula references piecemeal in calc

[GregB]

Hello,

In LO 3.5.7.2 Build ID: 350m1(Build:2) on ubuntu 10.04, I am trying to build a formula which includes a reference to a sheet in another workbook. I have a number of similar files, and would like to derive the name of the workbook separately, then append the cell or range, which would together constitute the desired reference. The approach that I am taking is to concatenate the pieces to form the whole reference. This gives me a reference error. In investigating, I have noticed that, while an explicit reference works, a quoted reference (a text string) gives a reference error:

=Sum(‘path_filename’#$‘sheetname’.range) works, whereas
=Sum("‘path_filename’#$‘sheetname’.range") gives a reference error.

I don’t know how I would compose my reference while avoiding identifying the elements as text strings which would eventually constitute a single text string (leading to a reference error), or alternatively to invoke a means by which the composed text string would be recognised as a valid reference.

Is there some way to have LO recognise a text string as a valid reference and/or to compose a reference which is not a concatenation of text strings?

Thank you.

[GregB]

Hi mariosv. Thanks; use it often. No good here: yields reference error. I entered the reference with and without enclosing double quotes with and without enclosing Indirect(). Sum(reference) works, but doesn’t answer my situation. Sum(“reference”) yields value error. Sum(Indirect(["]reference["]) yields reference error. Equally unusable results obtain when I put ["]reference["] in one cell and Sum([Indirect(]reference_cell[)]) in another cell.

[mariosv]

Hi GregB,

I think what you are looking for is the INDIRECT() function,
=Sum(INDIRECT("‘path_filename’#$‘sheetname’.range"))

look in the help to know more about the function.

It is very useful to build the link from content on other cells.