.. but this is more straight forward.
But are you
sure that suggested formula does anything like what you want?
1. For the example you gave, it returns (first 7 characters) 50.0
250 whereas you stated (quite correctly to me) it should be 50.0
150
Excel Workbook |
---|
|
---|
|
---|
1 | 50.0250.0001 | Reservoir | Contract | Structural | 0001 |
---|
|
---|
<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Code 2</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B1,{"Offsite","00";"Site/General","01";"Flow Control Vault","10";"Pre-Treatment","20";"Ozone","30";"LOX","31";"Filters","40";"Reservoir","50"},2</font>)&"."&VLOOKUP(<font color="Blue">C1,{"Contract","01";"Change Order","02";"Field Order","03";"Value Engineering","04";"Design Clarification","05";"Submittal","06";"Request for Information","07"},2</font>)&IFERROR(<font color="Blue">VLOOKUP(<font color="Red">D1,{"Civil","10";"Corrosion Protection","20";"Landscaping","30";"Architectural","40";"Structural","50";"Mechanical","60"},2</font>),"00"</font>)&"."&TEXT(<font color="Blue">ROWS(<font color="Red">$A$1:A1</font>),"0000"</font>)</td></tr></table></td></tr></table>
2. For the example below, by my reckoning the result should start with 00.0060 which is nothing like the formula result shown in A1 below?
Excel Workbook |
---|
|
---|
|
---|
1 | 50.0340.0001 | Value Engineering | Landscaping | Mechanical | 0001 |
---|
|
---|
<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Code 3</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B1,{"Offsite","00";"Site/General","01";"Flow Control Vault","10";"Pre-Treatment","20";"Ozone","30";"LOX","31";"Filters","40";"Reservoir","50"},2</font>)&"."&VLOOKUP(<font color="Blue">C1,{"Contract","01";"Change Order","02";"Field Order","03";"Value Engineering","04";"Design Clarification","05";"Submittal","06";"Request for Information","07"},2</font>)&IFERROR(<font color="Blue">VLOOKUP(<font color="Red">D1,{"Civil","10";"Corrosion Protection","20";"Landscaping","30";"Architectural","40";"Structural","50";"Mechanical","60"},2</font>),"00"</font>)&"."&TEXT(<font color="Blue">ROWS(<font color="Red">$A$1:A1</font>),"0000"</font>)</td></tr></table></td></tr></table>
Other comments/questions:
a) The suggested formula cannot produce the correct results, partly because it uses VLOOKUP without its final argument. For that to have any chance of working, the items in the first column of the lookup table must be in alphabetical order, which they aren't.
b) The formula doesn't reference the actual worksheet tables at all. So if later table items are to be added/removed/altered, the formula editing would be onerous.
c) In your example, did you mean to use just "Reservoir" in the final table when the earlier table had "
TW Reservoir"? Or should the values to be looked up exactly match what is in the data tables?
d) Are the "0001" type values in the final column of the result table already populated?
Assuming that the answer to c) is that the values should be exact (can amend if that is not the case) and the answer to d) is "Yes" then here is another formula that you could test. Or have I completely missed the point?
Formula in A2 is copied down.
Excel Workbook |
---|
|
---|
| A | B | C | D | E |
---|
1 | Code | Area | Component | Discipline | Item |
---|
2 | 50.0150.0001 | TW Reservoir | Contract | Structural | 0001 |
---|
3 | 00.0060.0002 | Value Engineering | Landscaping | Mechanical | 0002 |
---|
4 | 31.0000.0003 | LOX | Civil | | 0003 |
---|
5 | | | | | |
---|
6 | | | | | |
---|
7 | | | | | |
---|
8 | Code | Process Area | | | |
---|
9 | 00.0000.0000 | Offsite | | | |
---|
10 | 01.0000.0000 | Site/General | | | |
---|
11 | 10.0000.0000 | Flow Control Vault | | | |
---|
12 | 20.0000.0000 | Pre-Treatment | | | |
---|
13 | 30.0000.0000 | Ozone | | | |
---|
14 | 31.0000.0000 | LOX | | | |
---|
15 | 40.0000.0000 | Filters | | | |
---|
16 | 50.0000.0000 | TW Reservoir | | | |
---|
17 | | | | | |
---|
18 | | | | | |
---|
19 | Code | Process Area | | | |
---|
20 | 00.0100.0000 | Contract | | | |
---|
21 | 00.0200.0000 | Change Order | | | |
---|
22 | 00.0300.0000 | Field Order | | | |
---|
23 | 00.0400.0000 | Value Engineering | | | |
---|
24 | 00.0500.0000 | Design Clarification | | | |
---|
25 | 00.0600.0000 | Submittal | | | |
---|
26 | 00.0700.0000 | Request for Information | | | |
---|
27 | | | | | |
---|
28 | | | | | |
---|
29 | Code | Process Area | | | |
---|
30 | 00.0010.0000 | Civil | | | |
---|
31 | 00.0020.0000 | Corrosion Protection | | | |
---|
32 | 00.0030.0000 | Landscaping | | | |
---|
33 | 00.0040.0000 | Architectural | | | |
---|
34 | 00.0050.0000 | Structural | | | |
---|
35 | 00.0060.0000 | Mechanical | | | |
---|
36 | | | | | |
---|
|
---|