[solved] Checking values

Discuss the spreadsheet application

[solved] Checking values

Postby jimbokea » Sat Apr 27, 2019 2:53 am

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.
Reason: Add green tick
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: checking values

Postby FJCC » Sat Apr 27, 2019 3:41 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: checking values

Postby jimbokea » Sat Apr 27, 2019 4:14 am

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: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: checking values

Postby jimbokea » Sat Apr 27, 2019 4:57 am

Flag that - it only works intermittently
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: checking values

Postby jimbokea » Sat Apr 27, 2019 5:13 am

I can make the Ws and Ls turn red if they should be a "D"
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: checking values

Postby Zizi64 » Sat Apr 27, 2019 7:42 am

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 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; 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.
User avatar
Zizi64
Volunteer
 
Posts: 8149
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: checking values

Postby jimbokea » Sat Apr 27, 2019 9:19 am

which is what I did
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: checking values

Postby RusselB » Sat Apr 27, 2019 6:37 pm

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.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Checking values

Postby jimbokea » Mon Apr 29, 2019 12:39 am

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: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: Checking values [solved]

Postby robleyd » Mon Apr 29, 2019 2:16 am

Someone may have added them up worng.


A spreadsheet would not suffer from this problem ;)
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2885
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 16 guests