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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
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)
 

ADR2015

New Member
Joined
Feb 2, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

ADR2015

New Member
Joined
Feb 2, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
I'm not a VBA expert but I believe Cells.Interior.ColorIndex = 0 is what's removing the other colors.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770

ADVERTISEMENT

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:

ADR2015

New Member
Joined
Feb 2, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
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.
 

ADR2015

New Member
Joined
Feb 2, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

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
Top