How can I pull data from several worksheets?

[tgcali]

Hello,

I have a problem I hope someone can help me with. I have a workbook with 250
sheets for participants plus my summary sheet. I need a sign-in sheet that
will auto populate any participant listed as active.

I need to pull the same information for each participant and the information
is in the same place on each sheet. Now if that makes any sense, this is what
I need pulled:

ID# from cell B2
name from cell B1
Officer from cell H2
Start Date from cell B6

I need a sign-in sheet for both the Thursday and Friday groups. Whether or
not the participant is in the Thurs. or Fri. group is determined by the word
Thurs. or Fri in cell E3.

Wether they are active or not is determined by the word 'yes' in cell B41.

I need to be able to place the information in list form on the sign-in
sheet. ex:

B C D E F
ID# Name blank column Start Date Officer

Out of all 250 sheets there will never be more than 22 at a time for each
group and the list of participants changes every week based on who is listed
as active.

I don't know if this can be done or not. Any thoughts or suggestions would
be greatly appreciated. Thank you very much in advance.

tgcali

 

[Max]

Can you upload a sample file using a free filehost,
then post a link to it here?

Eg, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)

---

 

[tgcali]

Max,

Many thanks for the reply. I copied blank pages into a new workbook and took
out what I need to. Hopefully this is what you need.

Help Test.xls
tgcali

 

[tgcali]

Oops, sorry. You asked for the direct link. My apologies.

http://www.freefilehosting.net/download/3j5b4

Max,

Many thanks for the reply. I copied blank pages into a new workbook and took
out what I need to. Hopefully this is what you need.

Help Test.xls
tgcali

Can you upload a sample file using a free filehost,
then post a link to it here?

Eg, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)

 

[Max]

Here's a formulas driven set-up to automate it as desired ..

Illustrated in this sample (based on your earlier sample):
http://www.freefilehosting.net/download/3j5gh
Extract specific cells from multishts n autopop cols by criteria.xls

In Sign-In,
In A1, create a simple DV to select Thursday, Friday

In an adjacent area to the right of your template cols (I used cols K to T):
List the specific cell refs to be extracted from each sheet in L1:Q1, eg:
B2, B1, B6, H2, ..

List the sheetnames* in K2 down (in any order)
*If there's a LOT of sheetnames to list, you can run the sub (below) to list
it all in a new sheet (it'll be listed in A2 down), then easily copy n paste
over. Note that sheetnames listed need to match exactly with what's on the
tabs (except for case)

In L2: =IF(COUNTA($K2,L$1)<2,"",INDIRECT("'"&$K2&"'!"&L$1))
Copy L2 across to Q2, fill down as far as required
This extracts all specified data from each sheet

List the 2 dv droplist days in S1:T1, viz.: Thursday, Friday
In S2:
=IF($Q2="","",IF(AND($Q2=S$1,$P2="Yes"),ROW(),""))
Copy S2 to T2, fill down to the same extent as done for cols L to Q

Then, to populate the required fields in your template
In B2:
=IF(ROWS($1:1)>COUNT(OFFSET($R:$R,,MATCH($A$1,$S$1:$T$1,0))),"",INDEX(L:L,SMALL(OFFSET($R:$R,,MATCH($A$1,$S$1:$T$1,0)),ROWS($1:1))))
Copy B2 to C2, fill down

In E2:
=IF(ROWS($1:1)>COUNT(OFFSET($R:$R,,MATCH($A$1,$S$1:$T$1,0))),"",INDEX(N:N,SMALL(OFFSET($R:$R,,MATCH($A$1,$S$1:$T$1,0)),ROWS($1:1))))
Copy E2 to F2, fill down. Format col E as date

The above will auto-populate it as desired, depending on the day selected in
A1's DV. Results will be neatly bunched at the top

P/s: You need to clear up the extra trailing space in your existing droplist
for THURSDAY.

*Sub to list sheetnames
' ---- begin ---
Sub ListSheetNames()
Dim wkSht As Worksheet
Range("A2").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
' --- end ---

---

 

[tgcali]

Max,

Thank you so much for your help. I will try your solution this afternoon and
report back to you. Thanks again!

tgcali

 

[tgcali]

Max,

It works like a dream! I can not thank you enough. Have a great weekend!

Thank you!

tgcali

 

[Max]

Welcome, I'm delighted to hear that.