[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!



--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted