Whist score sheet frustration

ADR2015

New Member
Joined
Feb 2, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi. during lockdown we have been playing progressive whist online for nearly a year now. I've decided to record scores on a spreadsheet so all can see on zoom. i have managed to do a basic formula below, however there are some tweaks to it that i can't seem to resolve. Probably really simple but i am a beginner here. i have three columns basically, Bid, won and score. lets say columns A, B and C. Column A will contain the bid, values 0 to 7. B will show what they actually achieved, values 0 to 7 also. if the values match, column C should show a sum of 10 plus 2 points for every trick, i.e. call 2 win 2 points are 10+(2*2)=14 points. this needs to then be added to the value above in c to give running score. My formula does that, however if I add a 0 to a cell in A, and the adjacent cell in B is blank, the number 10 appears in the C column. i need to tell it to only do the calculation if both of the cells A and B have a number in it. if the two scores dont match then only the running score above is entered. Also if i dont want any figure at all to appear in the c column until the calculation is done, how do i show that.
The formula i did is =IF(L8=K8,(M7+10+(2*K8)),M7) assuming the cells im using are L, K and M

thanks

alan
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi ADR2015,

It was a little tricky to follow but is this what you seek?

ADR2015.xlsx
KLM
1BidWonScore
2
310
410
52214
614
7214
8214
92228
1028
Sheet1
Cell Formulas
RangeFormula
M3:M10M3=IF(AND(ISNUMBER(K3),ISNUMBER(L3),L3=K3),(M2+10+(2*K3)),M2)
 
Upvote 0
Hi ADR2015,

It was a little tricky to follow but is this what you seek?

ADR2015.xlsx
KLM
1BidWonScore
2
310
410
52214
614
7214
8214
92228
1028
Sheet1
Cell Formulas
RangeFormula
M3:M10M3=IF(AND(ISNUMBER(K3),ISNUMBER(L3),L3=K3),(M2+10+(2*K3)),M2)
Hi Toadstool

Thanks for the quick reply. Works perfectly!!!!! just with a slight wrinkle. sorry if table not as neat as yours. in the score column, how can you prevent any return (i.e. cell to remain blank) until both of the other cells have been completed? is that possible? just housekeeping really. thank you very much


ADR2015
CardsCallWonScore
1​
0010
2​
0020
3​
1132
4​
0042
5​
1242
6​
1242
7​
1042
bc4460
mis0080
nt0280
7​
80
6​
80
5​
80
4​
80
3​
80
2​
80
1​
80
80
 
Upvote 0
It's been over 50 years since I played Whist so I'm not sure if this is correct. Please take a lok:

ADR2015.xlsx
JKLM
1CardsBidWonScore
21000
32000
431112
540012
651212
761212
871012
9bc4430
10mis0030
11nt0230
127 
136 
145 
154 
163 
172 
181 
Sheet3
Cell Formulas
RangeFormula
M2:M18M2=IF(OR(K2="",L2=""),"",IF(AND(ISNUMBER(K2),ISNUMBER(L2),L2=K2,K2>0),(MAX($M$1:M1)+10+(2*K2)),MAX($M$1:M1)))
 
Upvote 0
Excellent, thanks. your second formula wasn't quite right, but i used your previous one and simply added the section =IF(OR(K2="",L2=""),"", to get the blanks where no scores have been added. This then works perfectly. thank you so much!!

As you are really helping, there is one more I would like to add. someone suggested it would be easier if the cell i was working on (i.e waiting for their call) would be highlighted. I used the VBA as follows; (their suggestion)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub

this does indeed highlights the selected cell cyan, but has the detrimental effect of removing all other colours on my spreadsheet. how can i modify this so that only the cell i am on at the moment is highlighted, but leaves all other colours alone, and then returns to no-fill colour when moves off it?

sorry if i have asked too many questions.

thanks.
 
Upvote 0
I'm not a VBA expert but I believe Cells.Interior.ColorIndex = 0 is what's removing the other colors.
 
Upvote 0
Try this for your Selection_Change highlighting:

VBA Code:
Public LastCell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    LastCell.Interior.ColorIndex = 0
    Target.Interior.Color = vbCyan
    Set LastCell = Target
End Sub
 
Last edited:
Upvote 0
Try this for your Selection_Change highlighting:

VBA Code:
Public LastCell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    LastCell.Interior.ColorIndex = 0
    Target.Interior.Color = vbCyan
    Set LastCell = Target
End Sub
Many thanks Eric. works fine, however doesn't work when the sheet is protected, even though the cells i access are unprotected.
 
Upvote 0
That seems like a bit of an odd choice for Microsoft, to not allow formatting of unprotected cells. But when you protect the sheet, you can check the "Format cells" box and the macro should work. Otherwise, we'd need to add some Unprotect/Protect lines to the macro.
 
Upvote 0
That seems like a bit of an odd choice for Microsoft, to not allow formatting of unprotected cells. But when you protect the sheet, you can check the "Format cells" box and the macro should work. Otherwise, we'd need to add some Unprotect/Protect lines to the macro.
Works perfectly. Thanks a million.... wish I had your knowledge....
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top