Multiple references

[GummifF]

Hi guys.

I'm trying here again because it´s an excellent forum for help with Excel.

I'm creating a golf tournament manager and wanted to add some pages to organize players into tee times.

What I'm trying to do is to connect multiple cells between sheets with some conditions that I require. It´s a bit complicated to go into detail but I simplified what I need and included a picture and sample workbook.

If you check out this image below then basically what I need to do is to be able to drag and drop the names from sheet 2 into column B or C which will then update automatically on sheet 3 to the left and right (A and D). Then, and this is important, I want to freely drag and drop from those (sheet 3) into places in such a way that players don's play the same course twice. The thing here is that I want cells in A and D to be locked to the CELLS in sheet 2 (as the image shows) so if I move any names in sheet 2, the cell references in sheet 3 will not move (change) with them. Is this possible?
I can´t figure this out and also, if I drag and drop the names in sheet 2 to columns B or C, then sheet 3 gives me a #REF! error which does not happen if I copy and paste values (easier if able to drag and drop).
Sheet 1 has to be there because it´s data from a separate registration sheet.
31726

Thank you...

[jdelano]

Try this out

Create a module and place this in it:


Public selectedCellAddress As String ' the starting cell address
Public selectedCellFormula As String ' what is in the selected cell
Public allowDragAndDrop As Boolean ' indicate it is okay to do the drag and drop


on Sheet2 use these two events


Private Sub Worksheet_Change(ByVal Target As Range)
If allowDragAndDrop And Target.Address <> selectedCellAddress Then
' user has performed a drag and drop
' set the previous cell back to the formula is was
Sheet2.Range(selectedCellAddress).Formula = selectedCellFormula
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' only save the info if the starting column is A
If TypeName(Selection) = "Range" And Target.Column = 1 Then
selectedCellAddress = Selection.Address
selectedCellFormula = Selection.Formula
allowDragAndDrop = True
Else
allowDragAndDrop = False
End If
End Sub


edit: then mimic this for Sheet3

[GummifF]

Thanks Jdelano but it does not work (unless I'm doing something wrong).

Did as you suggested and I still get #REF! error on Sheet 3 when I drag things around on sheet 2 PLUS it copies the contents of the cells when I drag and drop but I want to move them.
I have this almost working by using copy-paste values from sheet 2 to sheet 3 (gives me no REF errors) and then I can drag everything around on sheet 3 as I want but I'm limited to having to fill out all tee times on page 2 first (which is ok) but when I update the names on sheet 1, they only update on sheet 2 and not sheet 3 because I used copy/paste which removes the referencing.

[jdelano]

I misunderstood when you said you wanted to put the player on a course on sheet2 but keep the reference. You can't move the formula and still have the formula linked to sheet1.

I'd say that a UserForm would work the best then, you can use the worksheet as the database (cringe), how many players are you dealing with? You can then use Listboxes for players and courses.

[GummifF]

Don´t worry about it, thanks for the effort. I think what I want can´t be done. I will use my way since the only drawback is to manually update the name on sheet 3 in case of changes in the registration. There probably won´t be so many changes anyway after I have arranged everyone on the courses which I will do last minute. Referencing sheet 3 to cells in sheet2 that are referencing cells on sheet one is the thing that does not work as far as I can figure.