asked on

Autopopulate Cells in Excel based on a value in a drop down box

Hi,

I need to have a drop down box based on data in a worksheet. On a separate sheet I have a grid that I want to autopopulate with all the data that contains the value in the drop down box. The number of rows that will populate will vary.

So, how do I capture all the data based on a value and fill in excel cells and only the rows with data should be displayed.

Attached is a copy of the grid
Pricing-Worksheet.jpg

byundt

The VLOOKUP function is made for this type of problem. I assume that you have a lookup table with catalog number in the first column. If description is in the second column, you could return it with a formula like:
=IF($A2="","",VLOOKUP($A2,Sheet2!$A:$F,2,FALSE))

See the sample workbook for how it might work.

Brad
AutopopulateQ27679669.xls

Marilync1266

ASKER

Thanks, but the problem is the user will not be selecting the products. All customers, products and prices are listed on another worksheet. I want the user to select a customer and an excel form fills in with all the rows it finds for the selected customer - but in a form format.

byundt

If you want to display all the data for a particular customer, the easiest approach is to use an AutoFilter on the source worksheet to select that customer. If you then want to create a report, you would copy the visible rows from the autofiltered database and paste them on a new worksheet.

The above approach can be automated with a macro if you desire. The code will vary with the layout of your workbook, but might look something like:

'This code goes in code pane for worksheet containing Customer selection
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("A1")          'Watch this cell for selections
If Intersect(Target, targ) Is Nothing Then Exit Sub
If targ.Value <> "" Then CustomerReport targ.Value
End Sub

Open in new window

'This code goes in a regular module sheet
Sub CustomerReport(SCustomer As String)
Application.ScreenUpdating = False
With Worksheets("Data").Range("A1").CurrentRegion
    .AutoFilter Field:=1, Criteria1:=SCustomer    'Field is the column number for Customer. Criteria1 is customer number (or name)
    .Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    .AutoFilter
End With
End Sub

Open in new window

Choose a customer on the Master worksheet, and a report will be generated of their purchases.

Brad
AutopopulateQ27679669.xls

ASKER CERTIFIED SOLUTION

Marilync1266

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marilync1266

ASKER

my co-worker gave me the solution

A1 is the location of my drop down box
Sheet3 contains all of the data

This match statement is in cell A19
=MATCH($A$1,Sheet3!A:A,0)

This match statement is in cell B19
=MATCH($A$1,Sheet3!A:A,1)

Cells P21 - P36 contain row numbers 0-15

=IF($A$19+$P21>$B$19," ",INDIRECT("Sheet3!B"&$A$19+$P21))