## [solved] Checking values

### [solved] Checking values

Hi
Is there a way to have 2 cells in a spreadsheet compared and if they are equal then another cell should show "D" otherwise an error message pops up to say that it should be "D"
cheers Jim
Last edited by robleyd on Mon Apr 29, 2019 2:29 am, edited 2 times in total.
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: checking values

Let's say the two cells you want to be equal are A1 and B1. The formula
Code: Select all   Expand viewCollapse view
`=IF(A1 = B1; "D"; "A1 should equal B1")`

will display either D or the message.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7449
Joined: Sat Nov 08, 2008 8:08 pm

### Re: checking values

oK - I don't want the "D" to show
I need to show that if points are the same it should be a draw, not a win and loss
I have worked around it like this.
entries│ W│ 3│ L │3│ should be │D│3│D│3│
so I created and extra column to the right that puts a "d" there if the points are equal ( being the 3 & 3 in example)
I used the formula =IF(D2=B2;"d";'l')
I made this"l" a valid entry and therefore d an invalid entry so when they move on the another cell a pop-up that says "Should be a draw" pops up
I hide this column

cheers Jim
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: checking values

Flag that - it only works intermittently
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: checking values

I can make the Ws and Ls turn red if they should be a "D"
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: checking values

I can make the Ws and Ls turn red if they should be a "D"

Use the "Conditional Format" feature of the calc. You can set some conditions and user defined cellstyles to apply in the settings of the CF function.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.4; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8671
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: checking values

which is what I did
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: checking values

Are you wanting to change the W/L to a D if the numbers beside them are the same? If so, then you are out of luck, with the exception of (maybe) a macro., since a cell can not contain an entry (W/L) and a formula (to display D if the numbers are equal).
If the W/L/D can be done as a formula, based on the numbers beside the letters, then here's my suggestion:
1) The letter/number combination are in 4 different columns (I'm going to use A-D)
2) The letters are in the 1st and 3rd columns, thus making the numbers in the 2nd and 4th.
3) In A1 enter
Code: Select all   Expand viewCollapse view
`=if(B1=D1;"D";if(B1>D1;"W";"L"))`

4) In C1 enter
Code: Select all   Expand viewCollapse view
`=if(A1="D";"D";if(A1="W";"L";"W"))`

Adjust the row and column numbers for your spreadsheet.. without knowing the actual layout, I can't give a code that will not need modification.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5809
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Checking values

hi Russell = making the W & L turn red is enough of a warning. Just because the points values are the same doesn't necessarily mean they are correct. Someone may have added them up worng. The red warning makes the scores look at the score card to see what it really should be. Changed to D automatically is an option as you say, but it needs to be checked first.'Thanks Jim
OPenOffice 4.1.1 on Windows 7
jimbokea

Posts: 177
Joined: Fri Jul 15, 2011 1:10 am

### Re: Checking values [solved]

Someone may have added them up worng.

A spreadsheet would not suffer from this problem
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3098
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia