Okay, with your current layout, dragging the formulas down as a set is neither intuitive or ideal for what you want to achieve. If you drag them down as a set, it skips two rows of input data. You can, however, combine the formulas like below, turn on wrap text for column Q and expand your row height to 60 to achieve "3 lines of output for 1 line of input".
| Book1 1-18-2024.xlsm | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 1 | STR. | ID | TOP | ELV. | INV. | DIR. | ELV. | INV. | DIR. | ELV. | INV. | DIR. | ELV. | PIPE | |||||
| 2 | CI | 11 | FR: | INV. IN | W | INV. IN | N | INV. OUT | E | 18"RCP | CI#11 FR: INV. IN(W):'-18"RCP | ||||||||
| 3 | CI | 15 | FR: | INV. IN | N | INV. IN | E | INV. OUT | S | 12"RCP | CI#15 FR: INV. IN(N):'-12"RCP | ||||||||
| 4 | CI | 154 | FR: | INV. IN | E | INV. OUT | W | 0 | 0 | 12"RCP | CI#154 FR: INV. IN(E):'-12"RCP | ||||||||
| 5 | CI | 155 | FR: | INV. IN | E | INV. OUT | W | 0 | 0 | 18"RCP | CI#155 FR: INV. IN(E):'-18"RCP | ||||||||
| 6 | CI | 156 | FR: | INV. IN | E | INV. OUT | W | 0 | 0 | 12"RCP | CI#156 FR: INV. IN(E):'-12"RCP | ||||||||
| 7 | CI | 157 | FR: | INV. IN | E | INV. OUT | W | 0 | 0 | 18"RCP | CI#157 FR: INV. IN(E):'-18"RCP | ||||||||
| 8 | CI | 164 | FR: | INV. OUT | W | 0 | 0 | 0 | 0 | 12"RCP | CI#164 FR: INV. OUT(W):'-12"RCP | ||||||||
| 9 | CI | 165 | FR: | INV. IN | E | INV. OUT | W | 0 | 0 | 15"RCP | CI#165 FR: INV. IN(E):'-15"RCP | ||||||||
| 10 | CI | 167 | FR: | INV. OUT | E | 0 | 0 | 0 | 0 | 18"RCP | CI#167 FR: INV. OUT(E):'-18"RCP | ||||||||
| 11 | CI | 169 | FR: | INV. IN | N | INV. OUT | S | 0 | 0 | 13"RCP | CI#169 FR: INV. IN(N):'-13"RCP | ||||||||
Sheet6 | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q2:Q11 | Q2 | =A2&"#"&B2&CHAR(10)&C2&D2&CHAR(10)&E2&"("&F2&")"&":"&G6&"'"&"-"&N2 |
