We help IT Professionals succeed at work.

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

Marilync1266
Marilync1266 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Hello, my co-worker solved my issue.

He used the match code to find the data that matched the drop down box and then used an indirect statement to populate the rows.

Thanks,

Author

Commented:
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))