Issues with Calc INDIRECT function

I’ve reviewed issues I’m having with CALC INDIRECT function. I use CALC and MS in a work/home environment where workbooks .XLSX need to be created/modified at work using MS Excel, but I use CALC at home to modify them and create new work related workbooks. The INDIRECT function seems to be particularly problematic. AND I use it extensively at work.

I have a workbook that was created in Calc in ods format. So the indirect function should work. However, here is a demonstration workbook where it’s failing its basic function.

Rows 2-3 show a standard cell reference in a second sheet. Works as it should. BTW Column B shows the function in the Column A - so you can easily see what I was trying to do.

Row 3 is showing ‘building’ a function call to indirect and various options that I’ve read should address the issue. But they dont.

I’ve built the file in Excel - works fine but you open in Calc and it doesn’t… The attached was written in Calc for this Q.

test Calc v Excel.ods (16.4 KB)

2025-08-03T05:00:00Z

Welcome!
Please see what value you have selected for this option

thanks for the fast reply!
I’ll check it.
2025-08-03T05:00:00Z
I’m set to Excel A1

It’s more complicated than that.
You’ve got to click the custom settings, then [Details…] and “Reference syntax for string reference” = “Calc A1 | Excel A1” which interpretes Sheet!A1 and Sheet.A1 in the same way.