Populate a range based on the selection in one cell

[STCrosstown]

So I am trying get a range to populate based on the selection in a single cell. So in the pic below, i have two types of projects. Admine Projects and IT Projects. They have different cost categories listed in column A and B. Cell D2 is a data validation where you can select either "Admine Projects, or IT_Project". I want the range F2:F13 to populate with the categories for the respective projects based on the selection in cell D2.

So when I select "Admine Projects" in cell D2 I would like the range F2:F13 to populate with the information in the range A2:A13.

How can i do this?

[/URL][/IMG]

 

[Michael M]

Maybe this formula copied down

=IF($D$2="Admin Projects",A2,B2)

or a code

Sub MM1()
Select Case Range("D2").Value
    Case Is = "Admin Projects"
        Range("A2:A13").Copy Range("F2")
    Case Else
         Range("B2:B13").Copy Range("F2")
   End Select
End Sub

 

[STCrosstown]

Thanks Michael M, the code is working and i have it set up to ref 7 named ranges.

Follow up question, If i move the button that activates the macro to a different sheet it no longer works. How do i imbed worksheet designation as well?

Thanks much

 

[Michael M]

That's becasue it is trying to run on the sheet where the button resides.
To make it work for a different sheet you must refer back to that sheet
change the text in RED to the sheet name you want the code to refer to

Sub MM1()
dim ws as worksheet
set ws = Sheets("[color=red]sheet1[/color]")
with ws
 Select Case .Range("D2").Value
    Case Is = "Admin Projects"
        .Range("A2:A13").Copy .Range("F2")
    Case Else
         .Range("B2:B13").Copy .Range("F2")
   End Select
end with
End Sub