Solved Find Price Associated with Supplier

April 27, 2012 at 11:35:26
Specs: Windows 7
I have an Excel 2003 question. I buy ingredients from a pool of suppliers. I choose the supplier based on price and availability. This is a small sample of my spreadsheet for illustration purposes. My actual spreadsheet has 300 ingredients and 20 suppliers. I want to be able type in a supplier code as shown in the first column, and have Excel return the price for that supplier under the Purchase Price column. The prices in this column will feed other worksheets. I have looked at LOOKUP, INDEX, AND MATCH functions, but I don't see how these would work. Nested if functions won't work because Excel has a limit of 7 and I need 20. The only "solution" i found is to use the workaround for nested functions described in but I would have to create a ridiculous number of defined formulas, especially because I would also like to return other information in addition to the price. Would really appreciate some help on this.

code Item S1 S1Price S2 S2Price S20 S20Price Purchase Price
mrh Prod1 MRH $5.00 PBK $5.25 BNY $4.85
pbk Prod2 MRH $3.00 PBK $3.33 BNY $2.50
bny Prod3 MRH $7.10 PBK $6.54 BNY $8.95

See More: Find Price Associated with Supplier

Report •

April 27, 2012 at 13:25:08
✔ Best Answer
First, please click on the blue line at the end of this post and read the instructions on how to post data in this forum. Then, repost your data so that we can see how it is supposed to line up.

Second, if you are trying to pull multiple rows and multiple columns from a range based on the entry in a single cell, then I think an Advanced Filter and some VBA code might work for you.

The problem with an Advanced Filter is that you have to manually refresh it each time you change the criteria. In essence, you have to go through the steps to recreate the filter each time.

One way around this is to set up the Advanced Filter once and then use VBA to recreate it each time you change the criteria.

For example, I have a database table in A1:D22, my Criteria Range is G1:G2 and I want to output the filtered data starting in G6. When I change my criteria in G2, I want the filtered data in the table that starts in G6 to update to match the entry in G2.

To accomplish that, I would use a Worksheet_Change macro to recreate the filter whenever I change the value in G2.

With the ranges I described above, my code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$G$2" Then
    Range("A1:D22").AdvancedFilter _
       Action:=xlFilterCopy, _
       CriteriaRange:=Range("G1:G2"), _
       CopyToRange:=Range("G6:J6"), _
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question