Looking for a formula to insert column header text

sammipd

Board Regular
Joined
Jun 6, 2010
Messages
63
I must compile over 60 multipage paper surveys. The 1st question has 20 possible responses and the users marked all that applied. The easiest method I can come up with is to "x" the appropriate box and excel replace the "x" with the column title. But I can't get it to work. Can you suggest a formula? or another method?
Thank you!


dogcatbirdfishsnake
Survey 1dogcatfish
Survey 2catbirdsnake
Survey 3xxx

<tbody>
</tbody>


<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,667
Office Version
2010
Platform
Windows
I must compile over 60 multipage paper surveys. The 1st question has 20 possible responses and the users marked all that applied. The easiest method I can come up with is to "x" the appropriate box and excel replace the "x" with the column title. But I can't get it to work. Can you suggest a formula? or another method?
Thank you!


dogcatbirdfishsnake
Survey 1dogcatfish
Survey 2catbirdsnake
Survey 3xxx

<tbody>
</tbody>


<tbody>
</tbody>
So, you want the three x's to be dog, bird?

Put the following formula in G4:

=IF(B4="x", B$1,"")

Then, copy the formula across the next four columns (H, I, J, K). You can also copy it down if there are more survey than 3.

Once that is done, copy the new region, which you just added a formula, and paste it by value to B4.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
Or,

try to use custom cell format to achieve your target

1[ Asume your data put in A1:F4, with header "dog", "cat", "bird", "fish" and "snake" in B1:F1

2] Select B2:B4 >> Custom Cell Format, enter: ;;;"dog"

3] Select C2:C4 >> Custom Cell Format, enter: ;;;"cat"

4] Select D2:D4 >> Custom Cell Format, enter: ;;;"bird"

5] Select E2:E4 >> Custom Cell Format, enter: ;;;"fish"

6] Select F2:F4 >> Custom Cell Format, enter: ;;;"snake"

Then,

7] Enter "x" in any cells of range B2:F4, it will change to the header name.

Regards
Bosco
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,571
Messages
5,469,467
Members
406,654
Latest member
MR_EXCEL_16

This Week's Hot Topics

Top