I'm having a heck of a time with this situation. I'll describe the sheet as
best I can. I have 16 sections with 2 drop down lists in each section. Each
drop down list is from the same overall list of 32 teams. I select team 1
then team 2, this would be done is cells D3 and D4 for example.

Once both teams are picked, a formula generates the winning team based on a
simple randbetween formula
(=IF(OR(D3="",D4=""),"",IF(RANDBETWEEN(1,G3+G4)G3 ,D4,D3)). Cells G3 and G4
contain a number representing each team's odds of winning. Stupid, I know.

Here's the problem, since randbetween is volitile, I'm trying to hide the
column w/ the formula (C), and have it copy/special pasted/value in D6. A
helpful fellow from this forum gave me this formula:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C6"), rng) Is Nothing Then
If Target.Value < "" Then
Range("D6").Value = Target.Value
End If
End If
End If
EndMacro:
End Sub

But it only prints the last clicked team. Can anyone help me out?

If I understand correctly this macro will copy the value which has changed in
C6 to D6...

Why don't you start recording a macro, copy and paste special what you want,
stop the macro and run that whenever you want...


"JSnow" wrote:

I'm having a heck of a time with this situation. I'll describe the sheet as
best I can. I have 16 sections with 2 drop down lists in each section. Each
drop down list is from the same overall list of 32 teams. I select team 1
then team 2, this would be done is cells D3 and D4 for example.

Once both teams are picked, a formula generates the winning team based on a
simple randbetween formula
(=IF(OR(D3="",D4=""),"",IF(RANDBETWEEN(1,G3+G4)G3 ,D4,D3)). Cells G3 and G4
contain a number representing each team's odds of winning. Stupid, I know.

Here's the problem, since randbetween is volitile, I'm trying to hide the
column w/ the formula (C), and have it copy/special pasted/value in D6. A
helpful fellow from this forum gave me this formula:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C6"), rng) Is Nothing Then
If Target.Value < "" Then
Range("D6").Value = Target.Value
End If
End If
End If
EndMacro:
End Sub

But it only prints the last clicked team. Can anyone help me out?