Data selection from a table

[bindon87]

I'm looking for some help - I have a cross reference pricing table which based upon the selection will give a price.

What I would like is to enter the criteria in a number of selection boxes and for it to lookup the correct price. i.e. in the boxes I would have selected the criteria in green from a 3 drop down boxes and it would have given the price in red.
[TABLE="width: 507"]
<tbody>[TR]
[TD]Payment
[/TD]
[TD][/TD]
[TD]Product 1
[/TD]
[TD]Product 2
[/TD]
[TD]Product 3
[/TD]
[/TR]
[TR]
[TD]Method 1
[/TD]
[TD]Employee
[/TD]
[TD="align: right"]£4.90
[/TD]
[TD="align: right"]£7.95
[/TD]
[TD="align: right"]£9.70
[/TD]
[/TR]
[TR]
[TD]Method 1
[/TD]
[TD]Employee & Partner
[/TD]
[TD="align: right"]£9.80
[/TD]
[TD="align: right"]£15.90
[/TD]
[TD="align: right"]£19.40
[/TD]
[/TR]
[TR]
[TD]Method 1
[/TD]
[TD]Single Parent Family
[/TD]
[TD="align: right"]£8.35
[/TD]
[TD="align: right"]£13.50
[/TD]
[TD="align: right"]£16.50
[/TD]
[/TR]
[TR]
[TD]Method 1
[/TD]
[TD]Family
[/TD]
[TD="align: right"]£13.25
[/TD]
[TD="align: right"]£21.45
[/TD]
[TD="align: right"]£26.20
[/TD]
[/TR]
[TR]
[TD]Method 2
[/TD]
[TD]Employee
[/TD]
[TD="align: right"]£6.00
[/TD]
[TD="align: right"]£9.70
[/TD]
[TD="align: right"]£13.55
[/TD]
[/TR]
[TR]
[TD]Method 2
[/TD]
[TD]Employee & Partner
[/TD]
[TD="align: right"]£12.00
[/TD]
[TD="align: right"]£19.40
[/TD]
[TD="align: right"]£27.10
[/TD]
[/TR]
[TR]
[TD]Method 2
[/TD]
[TD]Single Parent Family
[/TD]
[TD="align: right"]£10.20
[/TD]
[TD="align: right"]£16.50
[/TD]
[TD="align: right"]£23.05
[/TD]
[/TR]
[TR]
[TD]Method 2
[/TD]
[TD]Family
[/TD]
[TD="align: right"]£16.20
[/TD]
[TD="align: right"]£26.20
[/TD]
[TD="align: right"]£36.60
[/TD]
[/TR]
[TR]
[TD]Method 3
[/TD]
[TD]Employee
[/TD]
[TD="align: right"]£8.20
[/TD]
[TD="align: right"]£8.70
[/TD]
[TD="align: right"]£17.85
[/TD]
[/TR]
[TR]
[TD]Method 3
[/TD]
[TD]Employee & Partner
[/TD]
[TD="align: right"]£16.40
[/TD]
[TD="align: right"]£17.40
[/TD]
[TD="align: right"]£35.70
[/TD]
[/TR]
[TR]
[TD]Method 3
[/TD]
[TD]Single Parent Family
[/TD]
[TD="align: right"]£13.95
[/TD]
[TD="align: right"]£14.80
[/TD]
[TD="align: right"]£30.30
[/TD]
[/TR]
[TR]
[TD]Method 3
[/TD]
[TD]Family
[/TD]
[TD="align: right"]£22.15
[/TD]
[TD="align: right"]£23.50
[/TD]
[TD="align: right"]£48.10
[/TD]
[/TR]
[TR]
[TD]Method 4
[/TD]
[TD]Employee
[/TD]
[TD="align: right"]£8.20
[/TD]
[TD="align: right"]£8.70
[/TD]
[TD="align: right"]£17.85
[/TD]
[/TR]
[TR]
[TD]Method 4
[/TD]
[TD]Employee & Partner
[/TD]
[TD="align: right"]£16.40
[/TD]
[TD="align: right"]£17.40
[/TD]
[TD="align: right"]£35.70
[/TD]
[/TR]
[TR]
[TD]Method 4
[/TD]
[TD]Single Parent Family
[/TD]
[TD="align: right"]£13.95
[/TD]
[TD="align: right"]£14.80
[/TD]
[TD="align: right"]£30.30
[/TD]
[/TR]
[TR]
[TD]Method 4
[/TD]
[TD]Family
[/TD]
[TD="align: right"]£22.15
[/TD]
[TD="align: right"]£23.50
[/TD]
[TD="align: right"]£48.10
[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help?

Thanks in advance.

 

[MickG]

Would you be happy to Use 3 Validation DropDowns and select "Payment", "Relative" then "Product" in that Order and then retrieve the "Price" in a certain cell (Say, "D1")

 

[bindon87]

Yes that would be fine

Thanks!

 

[MickG]

Try this example:-
https://app.box.com/s/s4vx1zcmddg7r5nwngiqac2ydgqja2ud

NB:- If you Decide to use this Option in your own sheet based on My sheet2 option you will need to do the following:-
(Or you could possibly Use My sheet modified to your requirements.)

1) Add the validation lists in A2 to C2 as per the example on you selected sheet.
2) Move the code as follows:-
Right click my sheet2 "Sheet Tab", Select "View Code" from the drop down. Vbwindow and code appear.
Copy code from code window.
Open your sheet (sheet with validation List), Right Click your sheet tab, select "View Code" Vb window appears.
Paste code into your sheet Vbwindow, Close VbWindow

NB:- My Sheet2 code specifically refers to Sheet1 as being the sheet with Your data in !!!!
The Sheet1 code refer to the current "Active sheet" with Your data.