Am having trouble pasting images but the data is as follows:
My Table:
DIMS | NB
(mm) | NB
(inch) | OD
(inch) | OD (mm) | WT | WT
(mm) | WT
(inch) | WT (SCH) |
SCH | 150 | 6" | 6.625 | 168.3 | 40 | 7.11 | 0.280 | FALSE |
<tbody>
</tbody>
Source data:
NPS mm | Nom. Pipe Size | OD inches | OD mm | Thickness
mm inches | STD | Sched | Kg/m | |
| | | | | | | | |
150 | 6" | 6.625 | 168.3 | 7.11 | 0.280 | STD | 40 | 28.26 |
150 | 6" | 6.625 | 168.3 | 7.11 | 0.280 | | STD | 28.26 |
150 | 6" | 6.625 | 168.3 | 10.97 | 0.432 | XS | 80 | 42.56 |
150 | 6" | 6.625 | 168.3 | 10.97 | 0.432 | | XS | 42.56 |
150 | 6" | 6.625 | 168.3 | 14.27 | 0.562 | | 120 | 54.21 |
150 | 6" | 6.625 | 168.3 | 18.26 | 0.719 | | 160 | 67.57 |
150 | 6" | 6.625 | 168.3 | 21.95 | 0.864 | XXS | XXS | 79.22 |
<tbody>
</tbody>
Arrays:
A4:A174 = NominalASTM
B4:B174 = InchASTM
D4:D174 = ODASTM
E4:E174 = WallASTM
F4:F174 = WTInchASTM
H4:H174 = SchASTM
I4:I174 = KgmASTM
My customers order pipe thicknesses in 3 different ways, in Imperial, Metric and in Schedule. In column
WT, I want to put the thickness however they have ordered it and then I want the next three columns to populate automatically with the three variants. To address this, I created column DIMS but I can't combine all the options into one formula. So basically, if I am working with Imperial, I put IMP under DIMS and enter, for example, 0.280 in the WT column and then I want the WT mm to update to 7.11, the inches to 0.280 and the Schedule to 40 (or STD).
In the above example, WTmm and WTinch work fine. Whether I enter IMP, MET or SCH, they are returning the correct values. The formulae in these cells are:
WTmm {=IF(M2="IMP",R2*25.4,IF(M2="MET",R2,IF(M2="SCH",INDEX(WallASTM,MATCH(N2,IF(SchASTM=R2,NominalASTM))))))}
WTinch {=IF(M2="IMP",R2,IF(M2="MET",R2/25.4,IF(M2="SCH",INDEX(WTInchASTM,MATCH(N2,IF(SchASTM=R2,NominalASTM))))))}
The issue with Sch is that the Metric or imperial value is not fixed. It differs based on the NB of the pipe - for example, STD in 6" is 7.11mm but in 8" it's 8.18mm. This is why I used an index/match formula.