Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 07 Oct 2018 20:20:41 +0200Can Indirect adressing use a cell value to change the "sheet" value?https://ask.libreoffice.org/en/question/168026/can-indirect-adressing-use-a-cell-value-to-change-the-sheet-value/ Good evening
The work here is to prepare a large spreadsheet for Cancer Research. Intended for physical chemistry undergraduates and doctorate researchers, the idea is to give them a map of the whole spreadsheet (an Index, a "yellow pages") so that they can select the z values from a different sheet that will be investigated compared to an x,y plot on the sheet where they work.
To do this, at the base of a column of data (of 248 rows), they're offered the column number of a different sheet (which they can look up in the 'map' or 'index'). The column above (for fetching that data) is programmed: =INDIRECT(ADDRESS(248,AQ264,1,,"Cancerdata"))
(this is the formula at cell AQ248 only, for the example).
In column AQ, AQ264 is the cell (highlighted in bright yellow) where they choose the other sheet column number. It works very well. The difficulty is that "cancerdata" is not the only sheet involved. It can be any other disease or indicator of disease.
Some user effort is required (of course), so it were ideal if they type once in one cell (correctly it's hoped) the name of the sheet to be fetched - Cancerdata; the intention is that their typing changes the "abcd" value in all the rows of the column above in quotes. Indirect adressing of a cell value to modify indirect adressing of a column to fetch data from a different sheet.
Without this, it's possible but laborious to edit the whole column above using 'find and replace'. I can do it to construct the spreadsheet (as author) yet have my doubts that users (3000 or more international) will find an IT specialist just around the corner to do it for them.
I hope this question is clear, ready for questions if not.
MatthewArchetypeSun, 07 Oct 2018 20:20:41 +0200https://ask.libreoffice.org/en/question/168026/Two parameters functions: slope, intercept, etc. do not work with indirect(address()) as one parameterhttps://ask.libreoffice.org/en/question/131818/two-parameters-functions-slope-intercept-etc-do-not-work-with-indirectaddress-as-one-parameter/Hi,
I have a strange problem with libreoffice calc latest version 5.4.1.2 under ubuntu 16.04 64 bit (build 5.4.1~rc2-0ubuntu0.16.04.1~lo0).
I would like to use some functions that take two range parameters as input (slope, intercept, rsq, etc.).
These functions work well if the parameters range are defined as usual:
`SLOPE(absolute_range1,absolute_range2), INTERCEPT(absolute_range1,absolute_range2)` where absolute_rangeX is like this `$A$10:$A$100.`
However, I would like to able to change the range on which the functions work. For this purpose I use ADDRESS and MATCH function:
`H1=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell)-1,COLUMN(cell),1)` and I correctly get the cell address, let say `$C$10`.
After, by using `H2=INDIRECT(H1 &":$C$100")` I can obtain the required range and I able to use it in functions that take one parameter as `SUM(H2), SUMPRODUCT(H2)`.
Unfortunately, it does not work with `SLOPE(H2,$A$10:$A$100), INTERCEPT(H2,$A$10:$A$100)` and `SUMPRODUCT(H2,$A$10:$A$100)` too.
So is there any intrinsic limit about using `INDIRECT(ADDRESS())` together with functions that take two range as parameters?
Thank you
P.S.
For curiosity, I tried also with the same version of libreoffice on windows and with excel 2016 and both do not work.erotavlasThu, 21 Sep 2017 10:29:15 +0200https://ask.libreoffice.org/en/question/131818/