Pulling text from dynamic list of worksheets into summary sheet

[Adam85]

Hi all,

Background

I have a workbook which is used to collect survey results. The workbook contains a button with macro that copies all 'survey' worksheets from other workbooks saved within a specified folder.

The survey has a list of questions with the following options; Agree, Somewhat agree, Somewhat Disagree, Disagree, N/A, the person filling in the survey puts an 'x' in the column which matches their answer. The number of people responding is dynamic and therefore number of 'survey' worksheets pulled in can change. Once the surveys have been pulled into the results workbook, peoples responses are added up in a worksheet called 'Results Table' using the following formula: =SUM(IFERROR(COUNTIF(INDIRECT("'"&Key!A$5:A$1000&"'!G7"),"x"),0))

This all works great.

The problem
The problem I have is, I also ask for any comments to support their responses. I want to pull each of the responses into the 'Results Table' either with a comma between or on a new line but within the same cell.

In the short term I have used the following formula : =CONCATENATE('Survey (12)'!L7, "-",'Survey (11)'!L7,"-",'Survey (10)'!L7,"-",'Survey (9)'!L7,"-",'Survey (8)'!L7,"-",'Survey (7)'!L7,"-",'Survey (6)'!L7,"-",'Survey (5)'!L7,"-",'Survey (4)'!L7,"-",'Survey (3)'!L7,"-",'Survey (2)'!L7,"-",Survey!L7)

This however falls apart each time I run the macro to pull in the 'survey' worksheets.

What I think I need is a macro which looks at the dynamic list of 'survey' worksheets that have been pulled in, looks up the appropriate cells on each worksheet and pulls in the text. I would like this all to work on the click of the button that pulls in the 'survey' worksheets if possible but happy to have another button if needed.

Can anyone help?

Thanks

 

[NdNoviceHlp]

Hi Adam85 and Welcome to the Board! U need to provide the folder name (whole address), the file name(s), the cell data location(s) and what location(s) that U want the data to be placed in and in what format (ie data in separate cells or comma separated). HTH. Dave