Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP(J9;Locations.B5:D7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP(J9;Locations.B5:D7;2)=VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel), Excel) - don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files, or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel) - Excel; don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files, or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel; don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files, files), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel; don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However, However I agree with @karolus' commant to your question, and you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel; don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However I agree with @karolus' commant comment to your question, and you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (but your file get incompatible with Excel; don't (don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files), files, but I'm afraid this won't save to XLS(X)), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However I agree with @karolus' comment to your question, and you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your K9.

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT:EDIT : When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files, but I'm afraid this won't save to XLS(X)), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However I agree with @karolus' comment to your question, and you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9;$Locations.$B$5:$D$7;2) to your =VLOOKUP($J9,$Locations.$B$5:$D$7,2) in K9.