Hello,
> 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!
You may used INDIRECT formula :
=IF($E10="x",INDIRECT(K4),0)
Gérard
--
Unsubscribe instructions: E-mail to [email protected]
In case of problems unsubscribing, write to [email protected]
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
