Creating a list of data pulled from multiple worksheets

**mzbreski** · 10-20-2012, 01:22 PM

Hello,

I have searched this forum for a solution to my problem and have only been able to find a partial solution. I have a workbook with 5 worksheets. Sheets 1-4 have identical format (each sheet has patient satisfaction data for an individual clinic = 4 clinics). I would like to have sheet 5 display a list of results for questions 1,2,3, etc. The primary problem, and reason for this post, is that I have about 30 questions and over 100 clinics, so copy/paste will take ages. I found a thread on here that provided code that will create a list of data from a single cell in each worksheet (http://www.excelforum.com/excel-gene...to-a-list.html), but I will need to pull data from multiple cells per sheet. I have attached a sample file with a more thorough explanation.

The workbook that I am working with is much larger than the sample with much more data. If I know how to manipulate the code to say "Pull the data from this series of cells from each sheet and place it here", then I can use generic code to create multiple lists on the sheet called "lists".

Thanks in advance to anyone who can help me with this.

Mike

**mike7952** · 10-20-2012, 04:44 PM

Give this a try

Please Login or Register  to view this content.

**FDibbins** · 10-20-2012, 05:33 PM

this will do it for you with formulas.

note, i added a question numbe column to each sheet, and then added "question x" above the 2 tables

you can then just copy question 2 table for all others. (you may need to adjust ranges)

let me know if this is something you can work with?

**copead** · 10-20-2012, 05:52 PM

I've attached an alternate solution using the indirect function and a summary below. Hope this helps.

Enter these formulas in the "List" sheet:
Cell D2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),2,FALSE)
Cell E2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),3,FALSE)
Cell F2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),4,FALSE)
Cell G2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),5,FALSE)

You can then drag the formula down the rows to get up to 100 clinics and copy across the columns for all 30 questions. Another option would be to keep all the data in one continuous array and then use a pivot table to analyze the data. The latter approach will provide you with greater flexibility in analyzing your data.

Thanks,
Alex