Selected Answer
There are two questions here.
- If you want duplicate letters listed in WHITE!L4:L10 you can't use the unique list in DATAW!C:C as a source. Instead you might create a list including duplicates. That can be done with a variation of the formula there. I would start with removing COUNTIF($C$1:$C1, SUBSTITUTE(Alphabet,"?","~?") & "") + from that formula. I haven't tested but think it will work. You might place this formula in column D and refer to that column in WHITE!L4:L10.
- Range names, like range addresses (or sheet names) must be unique. However, you can define a name to be visible only within a specific worksheet. That would allow you to have the same name on two or more sheets. However, a range name with a scope of tab WHITE can't be accessed from any other sheet. Excel creates names with a workbook-wide scope by default. They can be accessed from anywhere in the workbook but must be unique within that scope.
You differentiate between DATAW and DATAB by adding an identifying letter to the name (I would merge these data on one sheet!). You might do the same with ScoreSheetW and ScoreSheetB. You might then write formulas which take the root name "ScoreSheet" and add the first letter of the sheet's name to it. In this way you could use the same formulas on both sheets. I wonder if the extra volume in the formulas and the associated reduction in simplicity is worth the benefit.
Edit of 18 Jun 2019 ==============================
In the attached workbook I removed the twin lines that offended me, and I joined the two Data sheets into one. I moved all data to the new DATA tab and based all referencing on it. I created a lot of new named ranges. You may like to take a look at them in the Name Manager.
Then I ran into trouble. I don't know the game. I don't know the rules. I don't know what rules you are trying to implement. I improvised with regard to column AG:AJ and AM but couldn't figure out how the Tray is supposed to work or the range L4:L10. Of course, that is precisely what you were working on - I destroyed everything and created nothing new.
The new DATA tab is designed to be hidden in such a way that the user can't see or access it. Use the procedures ThisWorkbook.HideData and .ShowData to hide and show this sheet.
In the same location you will find the subs DisableBoardControl and EnableBoardControl. They refer to the control I established (via VBA) of what can be entered on the Boards. Most cell there are off limits to the user. Changes made to thme will be undone, mostly without notice, for now.
In the code module of the White sheet you will find the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
' 18 Jun 2019
Dim Approve As Boolean
' less frequently used ranges are listed last
If Not Application.Intersect(Target, Range("WhiteBoard")) Is Nothing Then
Approve = ApproveLetter(Target)
ElseIf Not Application.Intersect(Target, Range("WhiteTray")) Is Nothing Then
Approve = True
ElseIf Not Application.Intersect(Target, Range("WhiteNotes")) Is Nothing Then
If Target.Column <> Columns("V").Column Then
Approve = True
End If
ElseIf Not Application.Intersect(Target, Range("WhiteRems")) Is Nothing Then
If Target.Column <> Columns("H").Column Then
Approve = True
End If
ElseIf Not Application.Intersect(Target, Range("WhiteAnagrams")) Is Nothing Then
Approve = True
ElseIf Not Application.Intersect(Target, Range("WhiteName")) Is Nothing Then
Approve = True
ElseIf Not Application.Intersect(Target, Range("WhitePlayer")) Is Nothing Then
Approve = True
ElseIf Not Application.Intersect(Target, Range("StartDate")) Is Nothing Then
Approve = True
End If
If Not Approve Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub
In essence, this code determines where the user made a change, in the WhiteBoard range or the ranges WhiteTray, WhiteRems, WhiteAnagrams, WhiteName etc. For each of such changes there should be rules which, if broken, results in the rejection of the change. I created such a test for the entries in the WhiteBoard range.
If Not Application.Intersect(Target, Range("WhiteBoard")) Is Nothing Then
Approve = ApproveLetter(Target)
It says, If the change occurred in the WhiteBoard range then take the value of the variable Approve from the ApproveLetter function. That function is located in the Scrabble module where it is also available to be called from the event procedure running when a change occurs on the BlackBoard. I think you will be able to read the code and determine if it does what you want. I think the change should be denied if the letter entered isn't available anymore.
Rules for changes to the other ranges would have to be stated by you before I can program them. I don't for example, know how the Tray is filled or emptied. I would like to hide the tiles in AG:AJ when the letter isn't available anymore.
The point of my work was to change your project from being one that deploys worksheet functions only to one that relies on VBA. As you have already found yourself, the problems you have been facing are easier to solve when VBA is available. My problem therefore isn't to create any of the lists you have been describing but to make them work. In order to solve your problem, please describe the list, like "a list of all letters available to a player in which each letter is listed as many times as it is available". I got that pat, in fact, but I didn't create such a list (although I made space for it on the DATA tab) because I don't see how more than the first 8 letters in that list can be displayed in L4:L10, and the information that would provide would be identical to what is already shown on the right of the Board.
It would be helpful to know if the player, in fact, "draws" tiles and places them in his Tray. Perhaps L4:L10 should be scrollable. Perhaps the player can select tiles or they are dealt to him. Perhaps ...
