[Solved] INDIRECT function

[tactps]

Hi.

I usually work in Excel (boo hiss) and am having a problem writing an indirect fuction in Calc.

I have sheets named with each date, eg
08.12.2023
09.12.2023
10.12.2023

I have a normal function that picks up a cell in the prior sheet. For example, on my sheet 09.12.2023 in cell I2 I have the function
=+'08.12.2023'.d27

I would like to write an indirect function so that the name of the worksheet (08.12.2023 in this example) is picked up from another cell.

In other words, I have the text "08.12.2023" in cell c12. I would like to use that to determine which sheet my value is in.

In Excel I would write
=INDIRECT(C12&"!D27")

But I just can't seem to get the syntax right in OpenOffice.

Thank you for reading this, and hopefully helping me.

[FJCC]

This seems to work.

Code: Select all

=INDIRECT("'"&C12&"'" & ".D27")

The sets of quotes on either side of C12 are single quotes wrapped in double quotes. If you used underscores instead of periods in your sheet names, you would not need the single quotes.

[tactps]

Thank you. That's perfect.

[Lupp]

Never use sheet names that require such twisting. Use clean sheet names not containing spaces or any special characters.
The sheet-per-day design is doubtable anyway, but in cases where it may be justified the date should be given in the slightest acceptable variant of ISO 8601. In Your example I would suggest D_2023_12_08. You won't need the silly apostrophes then.

[tactps]

Thank you Lupp. Very sound advice.