VBA code to populate data related to drop down list

Hi All,

I am new to this thread.

I need to populate data in 5 cells based on the data of a dropdown list.
If I select Home in the dropdown then data in next 5 cells should display Home details of the person.
If I select Office in the dropdown then data in next 5 cells should display Office details of the person.

Could any one please help me with the hint of the code that I can use.

Thanks,
Sunil

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

INDEX/MATCH explanation
http://www.excelforum.com/excel-gene...ame-row.html#3

Originally Posted by JBeaucaire

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

INDEX/MATCH explanation
http://www.excelforum.com/excel-gene...ame-row.html#3

Thanks for your reply. I am new to excel programming and finding it a little bit difficult to write the function that I need. Could you help me writing the below function:

Cell A1(Dropdown): Yes, No
Cell A2: Home (if A1 = Yes)
Cell A2: Office (if A1 = No)

The moment I select Yes or No in A1 , A2 should automatically populate Office or Home. (Home and Office are hard coded values and should be written in the function it self. I am not writing Home and Office in some other part of the excel sheet.)

Could you please give me a basic function that I can copy in cell A2.
Please help.

Thanks,
Sunil

ExcelGyan, this is the programming forum and your question is clearly a "formula" need.

It's unclear what direction you're copying the cell A2, but based on your info in post #3:

=IF(A1="", "", IF(A1="No", "Office", "Home"))

Originally Posted by ExcelGyan

How can I run the VBA code based on the dropdown selection ?

By using a worksheet_change event macro in the sheet module.

Originally Posted by JBeaucaire

By using a worksheet_change event macro in the sheet module.

I tried with worksheet_change but it throws error. My code is as below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Value = "AIR" Then
      Worksheets("Sheet1").Range("D8").Value = "AIRPLANE"
ElseIf Target.Value = "BLC" Then
    Worksheets("Sheet1").Range("D8").Value = "BUILDING"
End If

End Sub

The dropdown list is at C9 cell and changes will be reflecting at D8 cell.
I am getting error in the above code at the point when I try to assign the value in D8 cell. I get error 9 as Subscript out of range. I get error after selection of value in the dropdown in cell C9.

Kindly suggest where am I wrong and what should I change.

Thanks,

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature and explained in the rules)

I've got a solution waiting to post. Please update post #8 as requested.

Worksheet_Change macros are triggered when a cell value is changed on the sheet. If the macro itself then writes a change onto the sheet, it triggers the macro again. Oops.

So it is typical that you turn off other macros until the ws_change macro is done with its work, then turn them back on. This avoids issues.

Also, typically ws_change macros need some code to first test that the correct cells were changed before it does anything else.

Lastly, you need to insure the macro performs properly if a bunch of cells are changed at the same time. Most people who are only watching one cell exit the sub if that happens, I like to loop through all the cells and see if one of them was actually the watched cell.

Having said that, here's how I would write your ws_change macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range

For Each cell In Target
    If cell.Address = "$C$9" Then
    Application.EnableEvents = False
        Select Case cell.Value
            Case "AIR"
                Range("D8").Value = "AIRPLANE"
            Case "BLC"
                Range("D8").Value = "BUILDING"
        End Select
    GoTo Done
    End If
Next cell

Done:
Application.EnableEvents = True
End Sub

sorry delte my comment