summary from different worksheets (VBA)

[Akakaboto]

Unfortunately I'm not a VBA user but I think I need it for this task.


So my HR department will start to send out interview question to some employees.

It will be something like this:
[TABLE="width: 233"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Question 1[/TD]
[TD]Answer A[/TD]
[/TR]
[TR]
[TD]Question 2[/TD]
[TD]Answer B[/TD]
[/TR]
[TR]
[TD].....[/TD]
[TD]....[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

One excel worksheet for each person.


then they would like to have a summary sheet looking like


[TABLE="width: 278"]
<tbody>[TR]
[TD][/TD]
[TD]Question 1[/TD]
[TD]Question 2[/TD]
[TD].....[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Answer A[/TD]
[TD]Answer B[/TD]
[TD].....[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD].....[/TD]
[TD].....[/TD]
[TD].....[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD].....[/TD]
[TD].....[/TD]
[TD].....[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col></colgroup>[/TABLE]

So basically everytime they add a new sheet with a persons answer 1 row should be added to the summary sheet with that persons name and answers.

what do you think is the easiest approach to do this and does anyone have a macro for it that I can try to modify once I have the exact layout of the templates?

 

[FormR]

Hi, how about something like this formula based offering.

The formula in B2 is copied down and across. As new sheets are added enter the sheet name (I've assumed it to be the persons name) in the next available row in column A and copy the formulas down from the row above.

Excel 2013/2016
	A	B	C	D	E	F
1	Name	Q1	Q2	Q3	Q4	Q5
2	Adam	Answer A	Answer B
3	John	Answer C	Answer D
Summary

Cell Formulas
Range	Formula
B2	=IF(INDEX(INDIRECT("'"&$A2&"'!$B$2:$B$100"),COLUMNS($B2:B2))="","",INDEX(INDIRECT("'"&$A2&"'!$B$2:$B$100"),COLUMNS($B2:B2)))

Excel 2013/2016
	A	B
1	Name	Adam
2	Question 1	Answer A
3	Question 2	Answer B
Adam

Excel 2013/2016
	A	B
1	Name	John
2	Question 1	Answer C
3	Question 2	Answer D
John

 

[Akakaboto]

Hi, how about something like this formula based offering.

The formula in B2 is copied down and across. As new sheets are added enter the sheet name (I've assumed it to be the persons name) in the next available row in column A and copy the formulas down from the row above.

Excel 2013/2016

	A	B	C	D	E	F
Name	Q1	Q2	Q3	Q4	Q5
Adam	Answer B
John	Answer C	Answer D

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #FFFF00"]Answer A[/TD]

[TD="align: center"]3[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(INDEX(INDIRECT("'"&$A2&"'!$B$2:$B$100"),COLUMNS($B2:B2))="","",INDEX(INDIRECT("'"&$A2&"'!$B$2:$B$100"),COLUMNS($B2:B2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2013/2016

	A	B

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Adam[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Question 1[/TD]
[TD="bgcolor: #FAFAFA"]Answer A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Question 2[/TD]
[TD="bgcolor: #FAFAFA"]Answer B[/TD]

</tbody>

Adam​

Excel 2013/2016

	A	B

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Question 1[/TD]
[TD="bgcolor: #FAFAFA"]Answer C[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Question 2[/TD]
[TD="bgcolor: #FAFAFA"]Answer D[/TD]

</tbody>

John​

Beautiful!! That will work perfectly and will be easy for me to adjust!