Hello,
I have a Calc document with a sheet that contains a summary of experiments and several other sheet containing measurements for each experiment.
Let’s say column B in the main sheet contains the sheet names of each experiment (N1, N2 etc). I want to import values from each sheet into a row each on column C, so that C2 has data from experiment 1, C3 has data from experiment 2 and so on.
I can get the value from one sheet with =N1.S$3 (assuming the data I want from sheet N1 is in cell S3).
=CONCATENATE(B2;".S$3) shows as N1.S$3 in C2, not as the value. =INDIRECT(CONCATENATE(B2;".S$3")), which I read on similar threads, throws a #REF! error.
Is INDIRECT even the correct idea?
Thanks
Cannot reproduce the issue, please attach an sample.ods
real table upload.ods (31.2 KB)
This is (a copy without useful lables of) the data I am working with. I haven’t been able to reproduce with an entirely new table either, but this one still has the issue. This used to be an Excel file originally if that helps.
Dont know why, but your real…ods persists to use Excel-Syntax ⇒
=INDIRECT( B2 & "!S3" )
IMHO thats a bug?!
@JoeyB you should create a bug-message on https://bugs.documentfoundation.org/enter_bug.cgi attach there your Example and tell, it was initially created in Excel
1 Like
And this is the key to the solution.
The INDIRECT function expects a “!” separator between the sheet name and the cell address for this document.
See “Reference syntax for string reference”
1 Like
See above. 
I searched the Options, but miss the Detail-button 
make sure there’s no space or extra chars in your cells …
