[solved] Checking values

[jimbokea]

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

[FJCC]

Let's say the two cells you want to be equal are A1 and B1. The formula

Code: Select all

=IF(A1 = B1; "D"; "A1 should equal B1")

will display either D or the message.

[jimbokea]

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

[jimbokea]

Flag that - it only works intermittently

[jimbokea]

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

[Zizi64]

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.

[jimbokea]

which is what I did

[RusselB]

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

=if(B1=D1;"D";if(B1>D1;"W";"L"))

4) In C1 enter

Code: Select all

=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.

[jimbokea]

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

[robleyd]

Someone may have added them up worng.

A spreadsheet would not suffer from this problem