Results 1 to 12 of 12

20150714, 14:50 #1
 Join Date
 Apr 2009
 Posts
 122
 Thanks
 19
 Thanked 1 Time in 1 Post
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!

20150714, 15:13 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,958
 Thanks
 422
 Thanked 1,606 Times in 1,450 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
Maudibe (20150714)

20150714, 15:38 #3
 Join Date
 Apr 2009
 Posts
 122
 Thanks
 19
 Thanked 1 Time in 1 Post
Thanks for the quick reply. It works great until I Autofill the other cells. I have attached the revised workbook.
Thanks!

20150714, 16:05 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,983
 Thanks
 157
 Thanked 776 Times in 707 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

20150714, 16:10 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,958
 Thanks
 422
 Thanked 1,606 Times in 1,450 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!
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
MOSTATE (20150715)

20150714, 16:14 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,983
 Thanks
 157
 Thanked 776 Times in 707 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.pngLast edited by Maudibe; 20150714 at 16:53.

20150714, 16:54 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,958
 Thanks
 422
 Thanked 1,606 Times in 1,450 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150714, 17:22 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,983
 Thanks
 157
 Thanked 776 Times in 707 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; 20150714 at 17:45.

The Following User Says Thank You to Maudibe For This Useful Post:
RetiredGeek (20150714)

20150714, 20:07 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,958
 Thanks
 422
 Thanked 1,606 Times in 1,450 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

20150714, 20:19 #10
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,983
 Thanks
 157
 Thanked 776 Times in 707 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

20150714, 21:12 #11
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,958
 Thanks
 422
 Thanked 1,606 Times in 1,450 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()
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150715, 09:07 #12
 Join Date
 Apr 2009
 Posts
 122
 Thanks
 19
 Thanked 1 Time in 1 Post
You are so awesome...this is just what I needed.
Thanks so much