Hello,
I've been trying to nut this out for hours now. I'm probably over complicating things.
Scenario
I have a cell that requires a value from a column in another table. This cell is called "FP Matrix"
Table - MadecoPriceGroupT
The FP Matrix value I require to be return is in the Table - "FP_PriceFactorT" Column "ID"
Formula required in Cells FP Matrix column needs to look at the values of MIN Price and MAX Price.
Is the value in "FP_PriceFactorT[B_Board Rate/M2]" >= "MIN Price" and <= "MAX Price"
If Yes then return the value in "FP_PriceFactorT[ID]"
If no then find the value that meets criteria.
e.g.
Thank you
Chief
I've been trying to nut this out for hours now. I'm probably over complicating things.
Scenario
I have a cell that requires a value from a column in another table. This cell is called "FP Matrix"
Table - MadecoPriceGroupT
All boards price list May-2021.xlsx | ||||||
---|---|---|---|---|---|---|
R | S | T | U | |||
6 | MadeCo Price Groups | MIN Price | MAX Price | FP Matrix | ||
7 | Carcass White | $8.90 | $9.06 | |||
8 | Group 1 | $13.65 | $14.72 | |||
9 | Group 2 | $15.60 | $16.33 | |||
10 | Group 2a | $0.00 | $0.00 | |||
11 | Group 2b | $19.12 | $21.84 | |||
12 | Group 2c | $22.70 | $26.40 | |||
13 | Group 2d | $27.80 | $30.77 | |||
14 | Group 2e | $32.61 | $33.72 | |||
15 | Group 3 | $35.00 | $37.70 | |||
16 | Group 4 | $40.91 | $40.91 | |||
17 | Group 5 | $43.00 | $44.23 | |||
18 | Group 6 | $45.99 | $48.07 | |||
19 | Group 7 | $51.06 | $51.67 | |||
20 | Group 8 | $56.65 | $59.86 | |||
21 | Group 9 | $64.48 | $65.10 | |||
22 | Group 10 | $69.80 | $75.17 | |||
23 | Group 11 | $80.34 | $84.30 | |||
24 | Group 12 | $87.55 | $92.29 | |||
25 | Group 13 | $100.34 | $100.78 | |||
ADMIN |
Cell Formulas | ||
---|---|---|
Range | Formula | |
S7:S25 | S7 | =MINIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]]) |
T7:T25 | T7 | =MAXIFS(DataTableT[$/m2],DataTableT[Price Group],[@[MadeCo Price Groups]]) |
The FP Matrix value I require to be return is in the Table - "FP_PriceFactorT" Column "ID"
All boards price list May-2021.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | I | N | O | |||||||||||||
7 | B_Board Rate / M2 | STYLE TYPES | SUPPLIER | ID | ||||||||||||
8 | $14.98 | # 2 - MadeCo Select | Polytec, Formica | 1 | ||||||||||||
9 | $22.14 | # 3 - Commodity Range HMR PB & MR MDF (Plain) | CHIEF, Polytec, Amerind | 2 | ||||||||||||
10 | $22.13 | # 3 - Commodity Range HMR PB & MR MDF (W/Grain) | CHIEF, Polytec, Amerind | 3 | ||||||||||||
11 | $22.95 | # 4 - Mid Range MR MDF (Plain) | Laminex | 4 | ||||||||||||
12 | $22.95 | # 4 - Mid Range MR MDF (W/Grain) | Laminex | 5 | ||||||||||||
13 | # 5 - Vinyl Flat No Routing Plain (Text) | Polytec | 6 | |||||||||||||
14 | # 5 - Vinyl Flat No Routing Plain (Matt) | Polytec | 7 | |||||||||||||
15 | # 5 - Vinyl Flat No Routing Plain (Gloss) | Polytec | 8 | |||||||||||||
16 | # 5 - Vinyl Flat No Routing W/Grain (Matt) | Polytec | 9 | |||||||||||||
17 | # 5 - Vinyl Flat No Routing W/Grain (Gloss) | Polytec | 10 | |||||||||||||
18 | # 5 - Vinyl Flat No Routing Metallic (Gloss) | Polytec | 44 | |||||||||||||
19 | # 6 - Vinyl Basic Routing Plain (Text) | Polytec | 11 | |||||||||||||
20 | # 6 - Vinyl Basic Routing Plain (Matt) | Polytec | 12 | |||||||||||||
21 | # 6 - Vinyl Basic Routing Plain (Gloss) | Polytec | 13 | |||||||||||||
22 | # 6 - Vinyl Basic Routing W/Grain (Matt) | Polytec | 14 | |||||||||||||
23 | # 6 - Vinyl Basic Routing W/Grain (Gloss) | Polytec | 15 | |||||||||||||
24 | # 7 - Vinyl Advanced Routing Plain (Text) | Polytec | 16 | |||||||||||||
25 | # 7 - Vinyl Advanced Routing Plain (Matt) | Polytec | 17 | |||||||||||||
26 | # 7 - Vinyl Advanced Routing Plain (Gloss) | Polytec | 18 | |||||||||||||
27 | # 7 - Vinyl Advanced Routing W/Grain (Matt) | Polytec | 19 | |||||||||||||
28 | # 7 - Vinyl Advanced Routing W/Grain (Gloss) | Polytec | 20 | |||||||||||||
29 | # 8 - Vinyl Recessed Centre Routing Plain (Text) | Polytec | 21 | |||||||||||||
30 | # 8 - Vinyl Recessed Centre Routing Plain (Matt) | Polytec | 22 | |||||||||||||
31 | $26.01 | # 4 - Mid Range MR MDF Sheen (Plain) | Polytec | 23 | ||||||||||||
32 | $30.72 | # 4 - Mid Range MR MDF Sheen (W/Grain) | Polytec | 24 | ||||||||||||
33 | $39.14 | # 4 - Mid Range MR MDF (Plain) | CHIEFs Panelart | 25 | ||||||||||||
34 | $39.14 | # 4 - Mid Range MR MDF (W/Grain) | CHIEFs Panelart | 26 | ||||||||||||
35 | $20.97 | # 4 - Mid Range MR MDF (Plain) | Formica | 27 | ||||||||||||
36 | $20.97 | # 4 - Mid Range MR MDF (W/Grain) | Formica | 28 | ||||||||||||
37 | $14.50 | Style #5 Holly and None IHD Price White Satin Raw | Madeco | 29 | ||||||||||||
38 | $14.50 | Style #5 Holly and None IHD Price White Satin Painted Light Pigment | Madeco | 30 | ||||||||||||
39 | $14.50 | # 2 - MadeCo Select | In-House | 31 | ||||||||||||
40 | $14.50 | Style #5 Holly and None IHD Price White Satin Painted Dark Pigment | Madeco | 32 | ||||||||||||
41 | $0.00 | BBT (3mm Mdf) | 33 | |||||||||||||
42 | #REF! | BBS (19mm flooring) | 34 | |||||||||||||
43 | $47.95 | # 9 - 18mm Createc G1S Solid | Polytec | 35 | ||||||||||||
44 | $58.20 | # 9 - 18mm Createc G1S Timberprint | Polytec | 36 | ||||||||||||
45 | $74.93 | # 9 - 18mm Createc G2S Solid | Polytec | 37 | ||||||||||||
46 | $84.92 | # 9 - 18mm Createc G2S Timberprint | Polytec | 38 | ||||||||||||
47 | $47.95 | # 9 - 18mm Crystal Gloss G1S Solid | Laminex | 39 | ||||||||||||
48 | $54.95 | # 9 - 18mm Crystal Gloss G1S Timberprint | Laminex | 40 | ||||||||||||
49 | $64.94 | # 9 - 18mm Crystal Gloss G2S Solid | Laminex | 41 | ||||||||||||
50 | $74.93 | # 9 - 18mm Crystal Gloss G2S Timberprint | Laminex | 42 | ||||||||||||
51 | $25.97 | # 4 - Mid Range MR MDF Lamiwood Silk (Plain) | Laminex | 43 | ||||||||||||
52 | $25.97 | # 4 - Mid Range MR MDF Lamiwood Silk (W/Grains & Patterns) | Laminex | 47 | ||||||||||||
53 | $22.97 | # 4 - Mid Range MR MDF Formica Gloss (Plain) | Formica | 48 | ||||||||||||
54 | $22.96 | # 4 - Mid Range MR MDF Formica Gloss (W/Grain & Patterns) | Formica | 49 | ||||||||||||
55 | $8.75 | # 1 - 16mm White HMR TEXTURE [Carcase Board] - Matching 1mm PVC Edge | Polytec | 50 | ||||||||||||
56 | $8.75 | # 1 - 16mm White HMR MATT [Carcase Board] - Matching 1mm PVC Edge | Polytec | 51 | ||||||||||||
57 | Custom | 52 | ||||||||||||||
58 | None Required | 53 | ||||||||||||||
59 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - Raw [ready to sand & polish] | Amerind | 54 | ||||||||||||
60 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Satin 30% [open grain] | Amerind | 55 | ||||||||||||
61 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain] | Amerind | 56 | ||||||||||||
62 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] - 2 Pac Gloss 100% [grain filled] | Amerind | 57 | ||||||||||||
63 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - Raw [ready to sand & polish] | Amerind | 58 | ||||||||||||
64 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Satin 30% [open grain] | Amerind | 59 | ||||||||||||
65 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain] | Amerind | 60 | ||||||||||||
66 | $0.00 | # 10 - Veneer Range MR MDF Natural [2400 x 1200 x 19] & Reconstituted [2700 x 1200 x 19] - 2 Pac Gloss 100% [grain filled] | Amerind | 61 | ||||||||||||
67 | $0.00 | # 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - Raw [ready to sand & polish] | Amerind | 62 | ||||||||||||
68 | $0.00 | # 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Satin 30% [open grain] | Amerind | 63 | ||||||||||||
69 | $0.00 | # 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Semi Gloss 60% [open grain] | Amerind | 64 | ||||||||||||
70 | $0.00 | # 10 - Veneer Range MR MDF Reconstituted [2700 x 1200 x 19] - 2 Pac Gloss 100% [grain filled] | Amerind | 65 | ||||||||||||
71 | $18.00 | # 1 - 16mm Black HMR TEXTURE [Carcase Board] - Matching 1mm PVC Edge | Polytec | 66 | ||||||||||||
72 | $18.00 | # 1 - 16mm Black HMR MATT [Carcase Board] - Matching 1mm PVC Edge | Polytec | 67 | ||||||||||||
73 | SPARE | 68 | ||||||||||||||
74 | Albedor - Thermolated Door Style 5 | Albedor | 69 | |||||||||||||
75 | Albedor - Thermolated Door Style 6 | Albedor | 70 | |||||||||||||
76 | Albedor - Thermolated Door Style 7 | Albedor | 71 | |||||||||||||
77 | Albedor - Thermolated Door Style 8 | Albedor | 72 | |||||||||||||
78 | Albedor - White Satin - Raw Door Style 5 | Albedor | 73 | |||||||||||||
79 | Albedor - White Satin - Raw Door Style 6 | Albedor | 74 | |||||||||||||
80 | Albedor - White Satin - Raw Door Style 7 | Albedor | 75 | |||||||||||||
81 | Albedor - White Satin - Raw Door Style 8 | Albedor | 76 | |||||||||||||
82 | Albedor - White Satin - Paint Door Style 5 | Albedor | 77 | |||||||||||||
83 | Albedor - White Satin - Paint Door Style 6 | Albedor | 78 | |||||||||||||
84 | Albedor - White Satin - Paint Door Style 7 | Albedor | 79 | |||||||||||||
85 | Albedor - White Satin - Paint Door Style 8 | Albedor | 80 | |||||||||||||
86 | Albedor - True Reflections Door Style 5-1 | Albedor | 81 | |||||||||||||
87 | Albedor - True Reflections Door Style 5-2 | Albedor | 82 | |||||||||||||
88 | Albedor - Ultra Finish Door Style 5-1 | Albedor | 83 | |||||||||||||
89 | Albedor - Ultra Finish Door Style 5-2 | Albedor | 84 | |||||||||||||
90 | Stylelite Doors | In House | 85 | |||||||||||||
91 | $17.50 | # 2 - MadeCo Select Standard | 86 | |||||||||||||
92 | $27.50 | # 2 - MadeCo Select Impressions | 87 | |||||||||||||
ADMIN |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B41 | B41 | =$AH$23 |
B42 | B42 | =#REF! |
B59,B63,B67 | B59 | =$EQ2+$EU$2 |
B60,B64 | B60 | =$EQ3+$EV$2 |
B61,B65,B69 | B61 | =$EQ4+$EW$2 |
B62,B66,B70 | B62 | =$EQ5+$EX$2 |
B68 | B68 | =$EQ12+$EV$2 |
Formula required in Cells FP Matrix column needs to look at the values of MIN Price and MAX Price.
Is the value in "FP_PriceFactorT[B_Board Rate/M2]" >= "MIN Price" and <= "MAX Price"
If Yes then return the value in "FP_PriceFactorT[ID]"
If no then find the value that meets criteria.
e.g.
Thank you
Chief