0

I have a spreadsheet with two header rows. I want to generate the "full column name" as shown on row 3.

       A    |    B    |    C    |    D    |   E     |   F    |
  ------------------------------------------------------------
1 |         W         |                   Y                  | 
2 |    2    |    4    |    3    |    4    |   10    |   12   |

3 |   W-2   |   W-4   |   Y-3   |   Y-4   |  Y-10   |  Y-12  |

I tried a simple concatenate and filled across, but only the first cell of a merged range contains the value. In other words, cells B1, D1, E1, and F1 are blank.

How can I generate the full column name for each column?

|improve this question
  • Can we assume that the cells with "W" and "Y" are merged cells and that the spreadsheet in question actually has considerably more columns than the example, thus rendering manual entry overly tedious? – BillDOe May 31 '18 at 20:03
  • Yes. The end user might also modify the headers later as needed. – Steven May 31 '18 at 21:01
3

Formula for A3 

=INDEX(1:1,,MAX(($A$1:A1<>"")*COLUMN($A$1:A1)))&"-"&A2

Enter as an array formula (complete with Ctrl-Shift-Enter), Then copy across as far as required

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.