Excel formula or vba code

[Danielwood1985]

Hi All,

Sorry if this has already been answered.

I have a spreadsheet that requires an end user to complete a list of questions. Starting from c3 down to c12 are header boxes they need to complete. Then the questions are from c15 down to c35. The user reads the question then types the answer in the corresponding box in column d.

To enhance this, I've been asked to explore how we can display the data neatly. However, I think to do this I need the questions in the columns, then the answers in the row below.

Is there a way, rather than transforming the form which has been agreed, that I can copy the data across into a hidden sheet in the correct format?

I can attach an example sheet from my personal laptop later if it helps.

Thanks

[#2 · Accepted solution]

@Danielwood1985 

Create a new sheet.

Select A1:U2.

Enter the formula

=TRANSPOSE('Sheet 1'!C15:D35)

where Sheet 1 is the name of the sheet with the questions. Press Ctrl+Shift+Enter to confirm the formula (this is probably not necessary in Excel in Office 365).