Ask Your Question

How can I insert a value in a cell depending on the value of another cell? [closed]

asked 2013-07-28 21:18:52 +0100

Ottawa Reiver gravatar image

updated 2015-10-26 22:08:56 +0100

Alex Kemp gravatar image

I have a spreadsheet where column A holds several values ;e.g. MU, AU, EU, MD, AD, ED, CLOSED. "U" stands for "Upstairs", "D" for "Downstairs".

I wish to execute the following pseudocode:

IF cell A'n' = MU or AU or EU THEN write "upstairs" to cell B'n' ELSE IF cell A'n' = MD or AD or ED THEN write "downstairs" to cell B'n' ELSE write "other" to cell B'n' ENDIF ENDIF

I've tried several versions of this code - =IF(D2="DU"or"NU","up","down") - but that gives me Err:508, so I suspect the problem is my ignorance of the proper syntax. Can anyone rectify my ignorance, please? Or point me to a good tutorial, at least.


edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 22:09:11.418521

1 Answer

Sort by » oldest newest most voted

answered 2013-07-28 21:55:17 +0100

m.a.riosv gravatar image

I think the better way it is doing with VLOOKUP()

B2: =IFNA(VLOOKUP(RIGHT($A2;1);{"U"|"Up";"D"|"Down"};2;0)&"stairs";"Other")

you need LibreOffice version 4.0 or above because the IFNA() function.
For a lower version it is a bit long formula:
B2: =IF(ISNA(VLOOKUP(RIGHT($A2;1);{"U"|"Up";"D"|"Down"};2;0));VLOOKUP(RIGHT($A2;1);{"U"|"Up";"D"|"Down"};2;0)&"stairs";"Other")

You need to set up the separators such it are defined in:
Menu/Tools/Options/LibreOffice Calc/Formula - Separtors.

edit flag offensive delete link more


Good answer, but my data sample has led you astray. My database also has 2 _and_ 3 character codes, e.g. AFD and ADD. So the substring operation doesn't work. By the way, the colour highlighting of the formula in the cell: what information can I glean from that?

Ottawa Reiver gravatar imageOttawa Reiver ( 2013-07-29 21:27:47 +0100 )edit

What's the problem for extend/adapt the formula to your needs?. I think you know also it is possible with VLOOKPU() use external references, as long and diverse as you can need. If you can not find how, let us know a sample of your data, and maybe someone can find how to do it.

m.a.riosv gravatar imagem.a.riosv ( 2013-07-29 23:32:59 +0100 )edit

Question Tools

1 follower


Asked: 2013-07-28 21:18:52 +0100

Seen: 2,794 times

Last updated: Jul 28 '13