Named Range not working properly across different sheets in Calc

[sagor]

I have a sheet with player names (called Players), which can dynamically increase as players are added. I have a MATCH function that looks for the last position in the player column (looking for an end value in column), and then build a text string containing the “range” of player names. In my current case, the range is defined as $B$3:$B$46 and located in Cell B63. I create a name range called “Play” which is defined as $Players.$B$63 (Document Global)

All fine with that. When I create a Validity cell, to select from the player list, I define the Validity as INDIRECT(Play). This works all fine and dandy in the “Players” sheet. I can select a name from the list of players. If I add or remove players, this selection list changes dynamically.

However, when I try the same Validity selection on a different sheet (Stats), it does not work. What happens is it selects an array from the current Stats sheet, from the B column, not the “Players” sheet B column as defined by the named range “Play”
I have tried putting the INDIRECT into the named range (Play defined as INDIRECT($Players.$B$63) with same results, it works only in the Players sheet, but in any other sheet it selects the local sheet column B.

Is this a bug or am I missing something else?

[mariosv]

Please edit your question and attach a sample file, reduce the size as much as possible without private information, and paste the information in Menu/Help/About LibreOffice, there is a copy icon.

[sagor]

Version of Calc:
Version: 24.2.5.2 (X86_64) / LibreOffice Community
Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Example spreadsheet attached (not sure if it will work)

Validate_Range_Name.ods (18.4 KB)

[sagor]

This “error” existed in previous version of Calc as well (7.6.7.2)

[mariosv]

You have not the sheet name in ‘Players.$B$28’, with ‘Players.$B$3:$B$14’ instead of $B$3:$B$14, seems it works.
Without the sheet name in the reference, there is no way for Calc to know the sheet where the range is, as usual, without the sheet it is in the actual sheet.

[sagor]

The named range “Play” has the sheet name in it, as does “Play2”. So I don’t understand what is wrong. The Validate works in the “Players” sheet, but not the “Stats” sheet. Both sheets have the same Validity selection.
Where would I add the sheet name in the “Stats” sheet?

[sagor]

Ok, trying a “Play3” named range with contents: INDIRECT($Players.$B$29)
where $Players.$B$29 is $Players.$B$3:$B$14
So I added the “Players” sheet name to the dynamic construct. Testing on Stats page, it seems to work now.

[sagor]

However, going back to original spreadsheet, getting a “REF” error. Will investigate more.
EDIT: followed same construct as example sheet, but still getting #REF! error with both methods. Must be a typo mistake somewhere or something. Recreated same named range, just using cell in original sheet of $B$63 which contains: $Players.$B$3:$B$46.
Confused…

[sagor]

My original spreadsheet keeps getting the #REF! error if I prefix the B63 cell with “$Players.”
Works without it, but only on that sheet. I cannot figure out why the example sheet I created works with the ‘$Players.’ prefix in ‘Players.$B$28’, but on my original sheet, adding the sheet name to Players.$B$63 causes the #REF! error.

[sagor]

I may have found the problem. The new example spreadsheet that I uploaded had all default settings. The sheet I’m working on had an option (Tools, Options, LibreOffice Calc, Formula) with the “Custom” checked for “Detailed Custom Settings”, “Details…” had “Convert also locale dependent” . Changing “Detailed Custom Settings” to “Default Settings” allows the prefix “$Players.” to work, whereas with Custom it does not.
Not sure why this option would do this, nor do I know why that option was selected in the first place. My locale is US.
EDIT: This spreadsheet was originally imported from Excel. I had to go back to “Custom”, but selected “Calc A1 | Excel A1” for Reference Syntax because there were Excel formulas with sheet names in my spreadsheet. Seems to work both ways now, does the Calc method first, then does Excel evaluation if the syntax matches Excel.