I am trying to reformat an existing excel sheet in a new sheet.
The workbook has 2 sheets. Sheet 1 is called Responses and sheet 2 is called Question.
The format of the Responses sheet is:
|---------------------|---------------------|------------------|------------------|
| ID | Question1 | Question2 | Question3 |
|---------------------|---------------------|------------------|------------------|
| 1 | 5 | 5 | 5 |
|---------------------|---------------------|------------------|------------------|
| 2 | 4 | 4 | 4 |
|---------------------|---------------------|------------------|------------------|
| 3 | 3 | 3 | 3 |
|---------------------|---------------------|------------------|------------------|
| 4 | 2 | 2 | 2 |
|---------------------|---------------------|------------------|------------------|
| 5 | 1 | 1 | 1 |
|---------------------|---------------------|------------------|------------------|
The format of the Question sheet is:
|---------------------|---------------------|------------------|
| ID | Question | Value |
|---------------------|---------------------|------------------|
| 1 | Question1 | 5 |
|---------------------|---------------------|------------------|
| 2 | Question1 | 4 |
|---------------------|---------------------|------------------|
| 3 | Question1 | 3 |
|---------------------|---------------------|------------------|
| 4 | Question1 | 2 |
|---------------------|---------------------|------------------|
| 5 | Question1 | 1 |
|---------------------|---------------------|------------------|
| 1 | Question2 | 5 |
|---------------------|---------------------|------------------|
| 2 | Question2 | 4 |
|---------------------|---------------------|------------------|
| 3 | Question2 | 3 |
|---------------------|---------------------|------------------|
| 4 | Question2 | 2 |
|---------------------|---------------------|------------------|
| 5 | Question2 | 1 |
|---------------------|---------------------|------------------|
| 1 | Question3 | 5 |
|---------------------|---------------------|------------------|
| 2 | Question3 | 4 |
|---------------------|---------------------|------------------|
| 3 | Question3 | 3 |
|---------------------|---------------------|------------------|
| 4 | Question3 | 2 |
|---------------------|---------------------|------------------|
| 5 | Question3 | 1 |
|---------------------|---------------------|------------------|
Formula to populate the ID from the Responses Sheet to the Question Sheet however it increments while I need it to follow the pattern in the Question table:
=IF(INDIRECT("Responses!A:A")=0,"",INDIRECT("Responses!A:A"))
So far I can only get the first 5 responses of each column and I am not sure how to get the header value that corresponds to it in the Question column.
Here is the formula that I have so far to populate the Value column from the Responses sheet however it is limited to the values in 1 column.
=IF(INDIRECT("Responses!B2:B40000")=0,"",INDIRECT("Responses!B2:B40000"))
Thank you!
INDEX(MATCH())
in the cells of the body of the table in the Response sheet... it would also be helpful if you edit your question to include your question – Alex M Jun 3 '20 at 21:13