Need Rows Data Based on selection in List Box

[#1]

Dear All,

I want to prepare a quotation based on data available.

Though there are 10000+ rows and 20+ columns for PriceTable, only few of them shown.

PriceTable:

ITEM	Supplier 01	Supplier 02	Supplier 03	Supplier 04
Item 01	£100.00	£80.00	£110.00	£90.00
Item 02	£200.00	£180.00	£190.00	£250.00
Item 03	£250.00	£280.00	£260.00	£275.00
Item 04	£300.00	£350.00	£310.00	£290.00
Item 05	£400.00	£390.00	£450.00	£410.00

BasicData:

Freight	Margin
0.00%	0.00%
0.50%	1.00%
1.00%	2.00%
1.50%	3.00%
2.00%	4.00%
2.50%	5.00%
3.00%	6.00%
3.50%	7.00%
4.00%	8.00%
4.50%	9.00%
5.00%	10.00%
6.00%	11.00%
7.00%	12.00%
8.00%	13.00%
9.00%	14.00%
10.00%	15.00%
11.00%	16.00%
12.00%	17.00%
13.00%	18.00%
14.00%	19.00%
15.00%	20.00%

From above BasicData, I need Freight and Margin both should be shown separately as list box properties with LED selection type.

Once I select one data from each list box, respective column in my FinalTable (i.e. Freight and Margin) will be appeared as data selected.

i.e. if I select 3% freight and 10% margin, FinalTable will be having all rows with 3% freight and 10% margin.

My Quotation should be as below.

• (Minimum price - Line wise) *(1+Fright%)/(1-Margin%)

Need Final Table should be as chart & straight table.

FinalTable:

ITEM	MIN (£)	Freight	Margin	Quotation (£)
Item 01	£80.00	3%	10%	£91.56
Item 02	£180.00	3%	10%	£206.00
Item 03	£250.00	3%	10%	£286.11
Item 04	£290.00	3%	10%	£331.89
Item 05	£390.00	3%	10%	£446.33

Thanks and looking forward for your quick response.

Regards,

Manish Kachhia

[CELAMBARASAN · Accepted solution]

Hi,

     Have a straight table

     Dimension:ITEM

     Expressions:

     =RangeMin([Supplier 01],[Supplier 02],[Supplier 03],[Supplier 04]) for Min(£)

     =Freight for Freight

     =Margin for Margin

     (Column(0) - Line wise) *(1+Fright%)/(1-Margin%) for Quotation (£)

Celambarasan

[#3]

Oh... so simple...

Thanks for your help...