=INDIRECT(A4 & ".O$17") doesn't work

[LCL999]

I’m working with LO Calc v. 5.3.4.2.
The O.S is Windows 10 on an MS Surface.
I don’t have enough points to upload a file, so I hope this description works.

On the first sheet, I call it “Master” I have a column of sheet names. ABC, DEF, GHI, etc.
The remaining sheets (ABC, DEF, GHI etc), each has data. I’m interested in the same address on each sheet, C4
Suppose C4 in ABC is 12, in DEF is 23, in GHI is 57 etc.

Then I want to end up with a table in Master, col.A the sheet names, col.B. the C4 data (12, 23, 57 etc.)
I can achieve this by entering =ABC.C4 in col.B beside the ABC in col.A, DEF.C4 in col.B beside the DEF in col.A etc.

However this is quite tedious if I have 50 sheets.
I have tried variations of the Indirect function, but none work. So what should I do?

As a related question, does Calc (or Excel for that matter) offer an “Execute” function. i.e. that evaluates a string and then, well, executes it? Then I could just enter =Execute(A4&“C$4”)

[Lupp]

In Calc there is no execute (better: EVALUATE) function, and there is none in LibO BASIC for general reasons. I also do not know a service or a method of an object type for this purpose offered by the uno api.
In one urgent case I used cells of a hidden sheet to which I assigned the respective formulae. Parsing, tokenising and evaluation are then done in the standard ways of spreadsheets.
I cannot advise concerning Excel.

[Lupp]

If you post a question or an answer under your user name I have a handle to see for enough “karma”. I cannot award karma to a wiki post. That means is of little use anyway since any contributor with karma >=300 can edit post by others after all. (Also no karma for comments.)

[Lupp]

Please always include your LibO version and the persistent format of the file you opened. OS and version may be helpful in specific cases, too. Wiki questions suffer from disadvantages. In this case: I cannot make sure you get enough “karma” to attach a sample file.

Please attach a simple but relevant example showing your issue for you as a spreadsheet file in the format you actually used.

(Creating a reference from an address by INDIRECT as you did the $ signs are unnecessary: Address is a text constant anyway.)

A construct as you included with the topic works for me in V5.4.0.2RC and always worked in older versions.
An error occuring sometimes (as I know from forum posts) is caused by forgotten whitespace (trailing spaces e.g.). In your case this should not be the reason.
Some guesses:
-1- You are working on a file saved in an alien format.
-2- You have set addressing style ‘Excel A1’ for some reason.
-3- In the real file you didn’t put the text constant “O$17” directly into the INDIRECT call, but referred to a cell containing it. If that cell also contains the doublequotes a #REF! error will occur.
-4- Same issue with sheetnames.
You see: Without a sample file I am stabbing in the dark.
My recent version of Calc accepts the Excel’s sheetname separator ! and at the same time Calc’s . under both ‘A1’ settings. Older versions don’t.

(Editing1:)
Another guess:
-5- You are using bad sheet names interpretable as a number or containing non-name characters like spaces or even worse. Such sheet names are displayed in a specific single-quoted style if occurring in references.
Sheet names for getting passed to concatenation with a dot and a cell address or a range address must be given without the apostrophes.
I just tested an had to experience that this is no longer true: INDIRECT now expects nonsense sheet names in the nonsense style. It may be a matter of Excel compatibility?
This makes any well designed user function returning sheet names taken from the sheet object itself rather useless.
I remembered wrong with some respects due to the fact that I personally never use sheet-names of that kind. In specific I mixed up the way a sheet name is to be passed as a string to the ADDRESS function (fifth parameter) and how INDIRECT expects it. Since the parameter given to INDIRECT also is a string (text, not a reference) I expected the sheet part do be handled in the same way as by ADDRESS. This was wrong and there would be an actual problem doing it that way. There was made a mess when silly non-names were allowed in sheet-name position, and there will not be rescue.

To avoid problems of the kind abolish silly bad sheet names: Stick to the classical name syntax: Start with a basic letter of the latin alphabet and continue with letters, decimal digits and underscores in arbitrary order and of arbitrary number.

[LCL999]

Thanks for that. I have updated the problem description. Hope that helps.

[Lupp]

Please try to make clear in such a case what was changed or added.
You did still not post the file format.

[LCL999]

Hi Lupp,

1. Many thanks for your time and extensive comments.
2. File format fyi is .ODS
3. All Sheet names are upper case latin characters.
4. I was not able to send you a copy of the file as my karma is only 1.
   NOW
   when I retried the command, it worked as expected!
   So either I had finger trouble and deceived myself
   OR
   The developers have fixed a bug in the interim

Anyway, please consider the question answered, and thank you.

[LCL999]

Format as posted now works.
So either I had finger trouble and deceived myself
OR
The developers have fixed a bug in the interim

[Lupp]

Would you mind to tell what you did in the interim. Was there an update? To what version?
(No update ==> No matter what developers did.)

[LCL999]

No, I did not and have not updated.
I probably experimented with a different sheet or part of a sheet, but that should not have made a difference.
It must have been my own lack of care.
(Which is odd as I’m older than you and have been working with computers since 1960. I should have more sense.)