Excel - Use Dropdown menu populate a column

Hello Everyone,

I have a worksheet (Cost Center) with a dropdown menu and column that I'm trying to populate with a list of items from worksheet (DataPMC) based on the selection from the dropdown. Can't use Vlookup because it runs horizontally. Here is what I have so far wasn't sure if I needed to code the worksheet in VBA or if someone has a solution for me.

Code:

---

=OFFSET(DataPMC!A1,1,1)

---

Thanks I attached the worksheet

Attachment 10983

Use HLOOKUP.

If you have more complex calculations or formulas start slowing down your workbook, then try using an onchange event:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Range("A1").Value = "Item1" Then
            ' Calculate Stuff
        ElseIf Range("A1").Value = "Item2" Then
            ' Calculate Stuff
        End If
    End If
End Sub

---

I tried the HLookup as well with the same issues. How does the "Change" event work with the data validation list? Does the "Item1" represent the first worksheet and A1 the dropdown cell. Same for "Item2" and can A1 represent the list such A1:A? Also would this code be placed in the developer on the worksheet Cost Center?

Use INDEX/MATCH. I'm not sure how you're wanting this to work. On the 'CostCenter' sheet, cell D3 has data validation list (merged with E3) containing project numbers. Since you have blanks in your 'DataPMC' sheet in column A there are blanks in the list. I'd recommend having a unique list of project numbers and using that for data validation.*

I'd also recommend not having blank calues in column A of your 'DataPMC' sheet. If you don't want them to show just add conditional formatting, but it plays hell on good data structuring to contain blanks like that.*

In addition you need a foreign key of Project Number as well as Deliverables. This is done easiest with a helper column which concatenates these two values. This should result in a unique value for every project and deliverable. Then you can use that to lookup your values on from your 'CostCenter' sheet.*

* These changes have been done in the attached file.

Attachment 10986