Message du 20/06/11 21:41
De : "Tom Cloyd"
A : "LibreOffice User's Help Forum"
Copie à :
Objet : [libreoffice-users] how to move text string into formula, from another cell
I just thought of a way to save myself considerable time, by calculating
part of the parameter list of a formula, then moving the result into the
formula. But...I'm not getting it to work, and I'm wondering if I'm
making some dumb error.
The original cell formula I'm trying to improve is this:
=IF($E10="x",'m6-20'.N$4,0)
Every week I have to manually alter this part of it: m6-20
That part is the name of another sheet, were the cell N$4 contains the
value I'm after. The sheet name references 'day of week', 'number of
month', and 'number of day in month'.
I'm now calculating the sheet name by using a cell containing month
number, a fixed array containing the day of the week, and a variable
array containing the number of day in month, and the result is perfect.
In the case of the formula above, I get a calculation result of
['m6-20'.N$4,0] (result is between the brackets - exactly as it appears
in the calculation cell).
Here's the calculation formula for the string:
=CONCATENATE("'",K2,K1,"-",K3,"',N$4")
I want to move that string into position in the formula above -
"=IF($E10="x",'m6-20'.N$4,0)". Here's what doesn't work:
* removing the string 'm6-20'.N$4 from the formula, then clicking on the
cell where the same string is a calculated result. All that does is put
the calculated string into the cell, dropping all other parts of the
formula.
* changing the original formulat to =IF($E10="x",&"K4",0) or
=IF($E10="x",&K4,0) - where K4 is the cell with the calculated text
string. Either of these result in an error 510.
* inserting the string calculation formula INTO the original formula:
=IF($E10="x",CONCATENATE("'",K2,K1,"-",K3,"',N$4"),0). Amazingly, this
just produces, again, the 'm6-20'.N$4 calculated string.
I don't know what to try next. Is this just not possible?
Thanks for any help!