Looking for a formula to insert column header text

[sammipd]

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!


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]bird[/TD]
[TD]fish[/TD]
[TD]snake[/TD]
[/TR]
[TR]
[TD]Survey 1[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD][/TD]
[TD]fish[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Survey 2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]bird[/TD]
[TD][/TD]
[TD]snake[/TD]
[/TR]
[TR]
[TD]Survey 3[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 523"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yky]

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!


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]bird[/TD]
[TD]fish[/TD]
[TD]snake[/TD]
[/TR]
[TR]
[TD]Survey 1[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD][/TD]
[TD]fish[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Survey 2[/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]bird[/TD]
[TD][/TD]
[TD]snake[/TD]
[/TR]
[TR]
[TD]Survey 3[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 523"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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

 

[sammipd]

Thank you both! They are wonderful (and easy) solutions!