Pull information from one worksheet to another

D

Debbie

I have information on many worksheets that are tied to if then formulaes.
So, I may have 30 rows of information but only 5 fit the criteria. This
would be the same for about 10 sheets in one workbook. What I want is for
the pieces of information that fit the criteria to be pulled from each of the
worksheets into one final worksheet.
 
C

CLR

It kinda sorta depends on how complicated your "criteria" is........probably
would be better to do your filtering on each sheet and then just linking in
those specific cells........

=IF(SHEET5!A1>0,SHEET5!A1,"")

Or better yet, if the criteria is the same for all sheets, then consolidate
all sheets into one and use Autofilter.......

Vaya con Dios,
Chuck, CABGx3
 
D

Debbie

Chuck,
I have students from a building that are listed on each sheet by classroom
teacher. There are criteria listed for determining if the student is
considered an at risk student. If they meet two of the criteria, then a #1
goes into the first column. Some classes will have a couple, some will have
many. However, I do this report for the entire district, which makes it
quite time consuming to filter each classroom teacher and then cut and paste
into a separate spreadsheet. I can't consolidate into one spreadsheet,
because then I could have 400 students listed on one sheet and each teacher
needs to look at their own information.

So, are you suggesting that I filter each sheet and then have the formula
attach to the filtered portion?
 
C

CLR

Hi Debbie..........
Your project sounds like a fun one, here's a few thoughts that might light
a light and/or make things easier......

1- It does not matter if 400 students or 10,000 students are on one sheet,
each Teacher can view only their own students by just using the AutoFilter,
assuming there is one column listing the Teachers name for each student.....
and the AutoFilter can probably be used also to set up the criteria to
filter and show only those students of interest. This is the preferred
method that I would use personally........

2-Should you want to get into writing code, it is possible with VBA to
interrogate many workbooks that are constructed the same and "extract" only
certain cells to consolidate into one master workbook. This can be done
automatically at just the push of a button after setting the
criteria......it is no small task, but is beautiful to see when it's set up
and working.

Hope this helps.........it's time for dinner now here in St. Petersburg
Florida.....it sure smells good!

Vaya con Dios,
Chuck, CABGx3
 
C

Chris Lavender

Hi Debbie and Chuck

Chuck's suggestion re VBA code would be my preferred option to create the
kind of report you seem to want. It will carry out the filtering, copying
and pasting very quickly (less than a second for this sample file).

Sample code (for a single workbook containing 3 sheets with data in 4
columns A to D and a 4th sheet for your results) :

Notes:
Sheets("Sheet4").Range("I1:I2") holds the filter criteria - in my sample
case I1 has 'ATRisk' entered (the column header for the other sheets where
your #1 formula is); I2 has '#1' entered.
Sheets("Sheet4").Range("J2") holds a formula ="A"&CountA(A:A)+1 which
tells VBA where the next free row is to paste the data.
The Calculate line is to make sure that this formula refreshes.
If you have dozens of sheets in the same workbook a neater solution than
just repeating the same code with different sheet references would be to put
in a For... Next loop, but repeating the code as below does the trick just
as well...

Sub extractdata()

Sheets("Sheet1").Range("A1:D700").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet4").Range("I1:I2"), Unique:=False
Sheets("Sheet1").Range("A2:D700").SpecialCells(xlCellTypeVisible).Copy
destref = Sheets("Sheet4").Range("J2").Value
Sheets("Sheet4").Range(destref).PasteSpecial xlValues
Sheets("Sheet1").ShowAllData
Calculate

Sheets("Sheet2").Range("A1:D700").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet4").Range("I1:I2"), Unique:=False
Sheets("Sheet2").Range("A2:D700").SpecialCells(xlCellTypeVisible).Copy
destref = Sheets("Sheet4").Range("J2").Value
Sheets("Sheet4").Range(destref).PasteSpecial xlValues
Sheets("Sheet2").ShowAllData
Calculate

Sheets("Sheet3").Range("A1:D700").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet4").Range("I1:I2"), Unique:=False
Sheets("Sheet3").Range("A2:D700").SpecialCells(xlCellTypeVisible).Copy
destref = Sheets("Sheet4").Range("J2").Value
Sheets("Sheet4").Range(destref).PasteSpecial xlValues
Sheets("Sheet3").ShowAllData
Calculate

Sheets("Sheet4").Select
Range("A1").Select
End Sub

Hope this helps. I can email you a sample workbook if it makes it easier...

Best rgds
Chris Lav (UK, up late, work tomorrow...)
 
D

Debbie

Chris,
Would it be easier if I sent to you my spreadsheet instead of you creating a
sample spreadsheet?
Thanks,
Debbie
 
Top