PDA

View Full Version : populate data based on a list



rcbricker
04-16-2007, 01:09 PM
I have attached a spreadsheet. I need sheet one to populate from data supplied on sheet two based on the data list on sheet one. When the user selects from the drop down all the information for only the data that correlates should port in. I have been rakign my mind to figure out this one and can't get it.

Kieran
04-16-2007, 09:26 PM
Try the attached tha uses data validation and dynamic range names to achieve what you want.

Please note that the table of parts and suppiers on sheet 2 has been changed.

The parts are listed vertically and the corresponding suppliers are in a continuous horizontal line next to the part number.

There is no limit on the number of parts - up to the length of the spreadsheet, however you will not be able to put more than 254 suppliers for each part, as the workbook does not have enough columns.

rcbricker
04-17-2007, 05:34 AM
Kieran,

Thanks for the reply. I looked at the spreadsheet and could not get anything to run. The formual in K27 is showing the formula and not the results. I have had this happen with Vlookup formuals before. Any idea what to do when a spreadsheet does not calculate a formula? The calculation option is set to automatic.

Thanks

Kieran
04-17-2007, 04:44 PM
The k27 formula in sheet 2 is jsut my workings during making up the solution. You can delete it.
Sorry for not cleaning it up.

Go to sheet 1.

Under the part column (a2) click int he cell. You should see a dropdown appear. Choose a value. Then go to b2 and the relevant supplier list should also appear via drop down.
It does use lookups, but hey are hidden in the named range formulas.

rcbricker
04-19-2007, 09:22 AM
Thanks for the explaination. What I need to do is select the vendor and then have all the data imported for the parts. So when I select vendor "a" all parts that go with that vendor are listed on sheet 1.

mdmackillop
04-19-2007, 01:32 PM
Paste the following code in the Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$2" Then
Application.EnableEvents = False
GetData Target
Application.EnableEvents = True
End If
End Sub

Sub GetData(Target As Range)
Range(Cells(2, 1), Cells(2, 1).End(xlDown)).ClearContents
With Sheets("Sheet2")
.Columns("B:B").AutoFilter Field:=1, Criteria1:=Target
.Columns(1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet1").Range("A1")
.Columns("B:B").AutoFilter
End With
End Sub