Indirect Reference to another sheet through text in cell

English
#1

Hi,

I’m concatenating a string address in one sheet (Named “Settings”), where the only dynamic value will be the row number in the address (see image below).

Example1

In the other sheets I have the months, that contain values I want to extract. When I concatenate the address + row number (e.g. $januari.AA + 7) and put this in the indirect function, I get an error.

I’ve looked through multiple solutions, but every single solution has ended with a reference or value error. How can I retrieve the value of Januari.AA7, when I’m working the “Settings” sheet?

The Function-Assistent does recognize the value, but it still displays a reference error.

Kind regards,

Calvin

Dynamic change of Sheet name by dragging down the cell
#3

Perhaps you wanted to write $januari.AA & 7 ? Like as

=INDIRECT($Settings.A2 & 7)

#4

I get an error when referencing it either like A2 (in the same sheet), or as $Januari.AA7 / AA & 7. The value works fine outside the indirect function…

Example2

Above is the function-assistant which shows me that it does know which value is in the other sheet. It just returns a reference error as a whole it seems?

#5

Judging by this screenshot, INDIRECT() is applied to the value of the cell Januari.D7, which is equal to 1. In other words, trying to calculate INDIRECT(1) - and this is an erroneous expression, there is no cell with the address “1” in the spreadsheet

It will probably be easier to discuss your problem if you attach a sample file with data and formulas to your question or to the next comment.

Oh yes, I almost forgot - Welcome!

#6

Judging by this screenshot, INDIRECT() is applied to the value of the cell Januari.D7, which is equal to 1. In other words, trying to calculate INDIRECT(1) - and this is an erroneous expression, there is no cell with the address “1” in the spreadsheet

This does make sense in my head. I just tried to reference this through the ADDRESS function, but it doesn’t seem to work. I’ll try to fix this keeping your comment in mind. Meanwhile I’ve created an example file. Please take a look at it if you have time!

Thanks for the welcome! :slight_smile:

IndirectExampleSheet.ods (9.8 KB)

#7

Oh, not “dot”, but “exclamation mark” (like Excel) =INDIRECT("Januari!A" & 2)

INDIRECT expects the exclamation mark as sheet name separator

4 Likes
#8

Amazing! You are a life-saver! I didn’t think to use the excel notation since I created the document in libreoffice?

Anyhow, thanks a lot for your help. Cheers!

#9

There seems to be a bug here, or documentation are wrong. See @mikekaganski comment.

Both functions still use the dot '.' sheet name separator with A1 notation.

A1 notation is the default.

#11

“INDIRECT uses the formula syntax defined for this document in Options|Calc|Formula; when it’s Calc A1, use dot to separate sheet from cell; when it’s Excel A1 or Excel R1C1, use exclamation” :slight_smile:

Unless detailed calculation settings are Custom,

in which case it’s defined under Detailed calculation settings dialog, available from there - and using its Reference syntax for string reference.

image
image726×492 34.8 KB

1 Like
#12

It does not say that. Quoting the paragraph in full:

In case of R1C1 notation, ADDRESS returns address strings using the exclamation mark ‘!’ as the sheet name separator, and INDIRECT expects the exclamation mark as sheet name separator. Both functions still use the dot ‘.’ sheet name separator with A1 notation.

This is still misleading though and not the full functionality supported. INDIRECT() expects the cell reference address in the notation that is configured under Tools → Options → Calc → Formula, Detailed Calculation Settings, Details, Reference syntax for string reference, which for this document is explicitly set to Excel A1 hence the ! exclamation mark is mandatory. If it was set to Use formula syntax it would follow that (in this document Calc A1), or if it was set to Calc A1 | Excel A1 it would try those in order.
See also Detailed Calculation .

#13

When I open the sample document (in an old Linux PC with LO 6.4.5.2) Formula sintax is set to Calc A1; Detailed Calculation Settings are in Default settings; but Details... button, show Excel A1. So, INDIRECT needs “!” as separator.
I must recheck […] in LO 7.1.8.1 on Windows 7. EDIT: Custom is checked, and “!” is needed.
On Windows 10, the dot is fine without any configuration change.

#14

LO 6.4 to 7.1 appear to have a bug that Default Calculation Settings says Default even if they aren’t.

On Windows 10, the dot is fine without any configuration change.

I doubt that with this document.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.