Hi,

I'm having difficulty linking the results of a formula to a cell that contains a drop down menu. In the file attached, you'll notice that I am pulling the data (color preference, # of colors) from the data table onto the player specific data table via an offset function. When I select Player 1, the responses automatically change because of an offset function, and the table is populated with the responses of player 1.

The offset function is:
J14 =offset(C5,$L$10,0)
J15 =OFFSET(D5,$L$10,0)
J16 =OFFSET(E5,$L$10,0)
J17 =OFFSET(F5,$L$10,0)

The match function is:

L10 =MATCH(J10,B6:B8,0)

The problem is, I want to be able to change these very responses on the Player specific data table via a drop down menu in addition to having the offset formula present. column M is an example of what I want (scroll over to see the drop down menus) but I can't figure a proper way to incorporate the offset formula and drop-down menu.

How can I create this link?

Thank you for the help! :)

On Sunday, June 30, 2013 11:16:03 PM UTC-7, arceaf wrote:
Hi,



I currently have a VBA that copies info from one sheet, and pastes it

onto another sheet. The code is bellow. Normally, the text in the

material is pasted onto cells A2, B2 and E2 when I run the macro (I

linked it to a button, so it runs when i click the button). I want the

same info to be copied but, insted, I want it to be copied to a row

below every time I click the button.



For example:

1 click - material is copied and pasted to A2, B2, E2

2nd click - the same material is copied, but onto cell A3, B3, E3

3rd click - the same material is copied, but onto cell A4, B4, E4

Try this.

Option Explicit

Sub YourButton()

Sheets("Sheet1").Range("C2").Copy
Sheets("Recommendations").Range("A100").End(xlUp). Offset(1, 0) _
.PasteSpecial xlPasteValues

Sheets("Sheet1").Range("G55").Copy
Sheets("Decision Matrix").Range("B100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues

Sheets("Sheet1").Range("H55").Copy
Sheets("Decision Matrix").Range("E100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues

End Sub

Regards,
Howard

Hi,

Am Mon, 1 Jul 2013 07:16:03 +0100 schrieb arceaf:

For example:
1 click - material is copied and pasted to A2, B2, E2
2nd click - the same material is copied, but onto cell A3, B3, E3
3rd click - the same material is copied, but onto cell A4, B4, E4

try:

Private Sub CommandButton2_Click()
Dim LRow As Long
Application.ScreenUpdating = False
With Sheets("Recommendations")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Range("C2").Copy
.Range("A" & LRow).PasteSpecial Paste:=xlPasteValues

Sheets("Decision Matrix").Range("G55:H55").Copy
.Range("B" & LRow).PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi again,

try:

Private Sub CommandButton2_Click()
Dim LRow As Long
Application.ScreenUpdating = False
With Sheets("Recommendations")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Range("C2").Copy
.Range("A" & LRow).PasteSpecial Paste:=xlPasteValues

Sheets("Decision Matrix").Range("G55").Copy
.Range("B" & LRow).PasteSpecial Paste:=xlPasteValues
Sheets("Decision Matrix").Range("H55").Copy
.Range("E" & LRow).PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2