Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Populate Cells with Row/Column Intersection

    Hello All,
    Sorry if the title is a bit confusing. My Class Cost worksheet has cells to enter the range and step, the pricing is based on the combination of the Range and Step. The Pricing Structure worksheet has the pricing for each range and step intersection. If I enter Range A01 and Step B in the Class Cost worksheet I would like Excel to go to Pricing Structure worksheet and find the intersection of A01 and B and populate cost of $748.50 in cell D2 of the Class Cost worksheet.

    I have attached the workbook with the sample data.

    Sorry if this is confusing...any assistance would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    MOSTATE,

    Here's the Formula: =VLOOKUP($B2,PricingTable,MATCH($C2,PricingSteps,0 ))

    Range Names Required:

    PricingSteps ='Pricing Structure'!$B$1:$E$1
    PricingTable ='Pricing Structure'!$A$2:$E$7


    MoState Vlookuip.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-07-14)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks for the quick reply. It works great until I Autofill the other cells. I have attached the revised workbook.
    Thanks!
    Attached Files Attached Files

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Mostsate,

    As an alternative, this works in D2 then copy down

    =INDEX('Pricing Structure'!$A$2:$E$7,MATCH(B2,'Pricing Structure'!$A$2:$A$7,0),IF(C2="A",2,IF(C2="B",3,IF (C2="C",4,IF(C2="D",5,"")))))

    You could use named ranges as well

    HTH,
    Maud

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    MOSTATE,

    My BAD! I forgot to check the results. You need to redefine the PricingSteps range name to:
    ='Pricing Structure'!$A$1:$E$1

    Sorry about that!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2015-07-15)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Using Named Ranges:

    In cell D2 then copy down:
    =INDEX(TRng,MATCH(B2,ColRng,0),IF(C2="A",2,IF(C2=" B",3,IF(C2="C",4,IF(C2="D",5,"")))))

    Where:
    TRng (the table range) ='Pricing Structure'!$A$2:$E$7
    ColRng (column lookup) ='Pricing Structure'!$A$2:$A$7

    HTH,
    Maud
    Mostate.png
    Last edited by Maudibe; 2015-07-14 at 17:53.

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    MOSTATE,

    One more iteration. This version uses Dynamic Range Names...this means that you can add rows & columns to your Pricing Structure page and everything will adjust automatically. I have set Limits though of Row 1001 and Column Z adding past either will not be captured but I figured that would be sufficient and help to quicken calculations.

    New Range Name Values:

    PricingSteps =OFFSET('Pricing Structure'!$A$1,0,0,1,COUNTA('Pricing Structure'!$A$1:$Z$1))
    PricingTable =OFFSET('Pricing Structure'!$A$2,0,0,COUNTA('Pricing Structure'!$A$2:$A$1002),COUNTA('Pricing Structure'!$A$1:$Z$1))


    The formula does not change!.

    Test File: Populate Cells with Column Row Intersection3.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    RG,

    Take a look at your formula when you place a non valid Range in column B (cell B4)

    Mostate2.png

    It is only finding an approximate match on the first 3 characters of the search values in column B. I thing you need to add the FALSE statement to your formula

    =VLOOKUP($B2,PricingTable,MATCH($C2,PricingSteps,0 ),FALSE)
    Last edited by Maudibe; 2015-07-14 at 18:45.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-07-14)

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    Right! Thanks for catching that.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    RG,

    My pleasure. I like the way you used the Match function to find the column. Much more efficient than my conditional embedded if statements.

    Maud

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    Of course you could check your input by adding this to the "Class Cost" Worksheet Module:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim wksPS As Worksheet
       Dim vTest As Variant
       
       If Target = "" Then Exit Sub
       
       Set wksPS = ActiveWorkbook.Sheets("Pricing Structure")
       
       If Not Intersect(Target, Range("B:B")) Is Nothing Then
       
         On Error Resume Next
         vTest = Application.WorksheetFunction.Match(Target, wksPS.Range("A2:A" & _
                      Range("PricingTable").Rows.Count + 1), 0)
         On Error GoTo 0
         
         If vTest = "" Then
           MsgBox "Value " & Target & " Entered at: " & _
                    Target.Address(, , xlA1) & vbCrLf & _
                  "is NOT in the Pricing Structure Table..." & _
                  vbCrLf & "Please Correct...", _
                  vbOKOnly + vbCritical, _
                  "Error: Unknown Pricing Range"
           Target.Value = ""
         End If
         
       End If
       
          If Not Intersect(Target, Range("C:C")) Is Nothing Then
       
         On Error Resume Next
         vTest = Application.WorksheetFunction.Match(Target, Range("PricingSteps"), 0)
         On Error GoTo 0
         
         If vTest = "" Then
           MsgBox "Value " & Target & " Entered at: " & Target.Address(, , xlA1) & vbCrLf & _
                  "is NOT in the Pricing Structure Table as a Step..." & vbCrLf & _
                  "Please Correct...", _
                  vbOKOnly + vbCritical, _
                  "Error: Unknown Pricing Step"
           Target.Value = ""
         End If
         
       End If
    
    End Sub  'Worksheet_Change()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #12
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    You are so awesome...this is just what I needed.

    Thanks so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •