| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Pull information from one worksheet to another
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. |
|
#2
|
|||
|
|||
|
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!A10,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 "Debbie" wrote in message ... 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. |
|
#3
|
|||
|
|||
|
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? "CLR" wrote: 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!A10,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 "Debbie" wrote in message ... 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. |
|
#4
|
|||
|
|||
|
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 "Debbie" wrote in message ... 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? "CLR" wrote: 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!A10,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 "Debbie" wrote in message ... 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. |
|
#5
|
|||
|
|||
|
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(xlC ellTypeVisible).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(xlC ellTypeVisible).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(xlC ellTypeVisible).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...) "CLR" wrote in message ... 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 "Debbie" wrote in message ... 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? "CLR" wrote: 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!A10,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 "Debbie" wrote in message ... 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. |
|
#6
|
|||
|
|||
|
Chris,
Would it be easier if I sent to you my spreadsheet instead of you creating a sample spreadsheet? Thanks, Debbie "Chris Lavender" wrote: 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(xlC ellTypeVisible).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(xlC ellTypeVisible).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(xlC ellTypeVisible).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...) "CLR" wrote in message ... 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 "Debbie" wrote in message ... 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? "CLR" wrote: 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!A10,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 "Debbie" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I retrieve deleted information from an Excel worksheet? | Excel Worksheet Functions | |||
| Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
| Worksheet Revision Date only once that day | Excel Discussion (Misc queries) | |||
| How to create a fill down that increments based on the worksheet n | Excel Worksheet Functions | |||
| How do I pull information from multiple existing spreadsheets int. | Excel Discussion (Misc queries) | |||
Hybrid Mode
