Pulling text from dynamic list of worksheets into summary sheet

Adam85

New Member
Joined
Nov 27, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,773
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top