| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to import data based on a variable
I have a basic spreadsheet and need to import data from that sheet into a new
sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
Hi, why not to use a auto filter?
does it helps? regards from Brazil Marcelo "kjp55" escreveu: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
I could but can I still get the totals rows at the bottom to appear somehow?
When I tried the autofilter, the last line, which is grand totals, disappeared and I couldn't make it apear until I took the filter off. "Marcelo" wrote in message ... Hi, why not to use a auto filter? does it helps? regards from Brazil Marcelo "kjp55" escreveu: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
try to create a subtotal one row after the end of the data.
does it helps? Marcelo "kjp" escreveu: I could but can I still get the totals rows at the bottom to appear somehow? When I tried the autofilter, the last line, which is grand totals, disappeared and I couldn't make it apear until I took the filter off. "Marcelo" wrote in message ... Hi, why not to use a auto filter? does it helps? regards from Brazil Marcelo "kjp55" escreveu: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
Yes, that worked but it still shows the totals for the entire columns, all
records. Is there a formula to make it only calculate and show a sub-total of the filtered records, instead of showing the sum of 'all' records? And then still having the master total of all records at the bottom when 'all records' is selected in the filter? Thanks for your patience. "Marcelo" wrote in message ... try to create a subtotal one row after the end of the data. does it helps? Marcelo "kjp" escreveu: I could but can I still get the totals rows at the bottom to appear somehow? When I tried the autofilter, the last line, which is grand totals, disappeared and I couldn't make it apear until I took the filter off. "Marcelo" wrote in message ... Hi, why not to use a auto filter? does it helps? regards from Brazil Marcelo "kjp55" escreveu: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
You will not be able to find a formula, but I build a subroutine that you can
use to get you started. it assumes 9 colums, I used your example as a reference. The only thing I added was naming the sheet where the base data is as "Source". Cheers, Sub Macro1() Dim iLastOp As Integer Dim wks As Worksheet Dim wksdest As Worksheet Dim rngOperat As Range Dim rngFiltOperat As Range Dim Scell As Range Dim Wkname As String ThisWorkbook.Application.ScreenUpdating = False iLastOp = Range("C65536").End(xlUp).Row Set wks = ThisWorkbook.Worksheets("Source") wks.Range("K:K").ClearContents wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1" _ ), Unique:=True iLastOp = wks.Range("K65536").End(xlUp).Row Set rngOperat = Range("K2:K" & iLastOp) For Each rngOperat In rngOperat ThisWorkbook.Worksheets.Add Worksheets(1).Name = rngOperat Next rngOperat Set rngFiltOperat = wks.Range("K2:K" & iLastOp) wks.Select wks.Columns("C:C").AutoFilter For Each rngFiltOperat In rngFiltOperat wks.Select wks.Range("C65536").End(xlUp).Select With Selection .AutoFilter Field:=1, Criteria1:=rngFiltOperat End With Wkname = rngFiltOperat Set wksdest = ThisWorkbook.Worksheets(Wkname) Cells.Copy wksdest.Paste wksdest.Range("K:K").ClearContents Next rngFiltOperat wks.Select wks.Range("C:C").AutoFilter wks.Range("K:K").ClearContents ThisWorkbook.Application.ScreenUpdating = True End Sub "kjp55" wrote: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
try to use
subtotal(9,g2:g90) adjust the range as need. does that help? regards from Brazil Marcelo "kjp" escreveu: Yes, that worked but it still shows the totals for the entire columns, all records. Is there a formula to make it only calculate and show a sub-total of the filtered records, instead of showing the sum of 'all' records? And then still having the master total of all records at the bottom when 'all records' is selected in the filter? Thanks for your patience. "Marcelo" wrote in message ... try to create a subtotal one row after the end of the data. does it helps? Marcelo "kjp" escreveu: I could but can I still get the totals rows at the bottom to appear somehow? When I tried the autofilter, the last line, which is grand totals, disappeared and I couldn't make it apear until I took the filter off. "Marcelo" wrote in message ... Hi, why not to use a auto filter? does it helps? regards from Brazil Marcelo "kjp55" escreveu: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
Thanks. I added the sub to the worksheet and got a compilation error all
highlighted in red when I ran the compiler)... [this line is highlighted as incorrect by the compiler] wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1" _ ), Unique:=True ..... seems this statement is not valid. As I know little about Macros and subroutines, I'm not sure what I'm looking at. Do I need to change something to make it work? "Michael" wrote in message ... You will not be able to find a formula, but I build a subroutine that you can use to get you started. it assumes 9 colums, I used your example as a reference. The only thing I added was naming the sheet where the base data is as "Source". Cheers, Sub Macro1() Dim iLastOp As Integer Dim wks As Worksheet Dim wksdest As Worksheet Dim rngOperat As Range Dim rngFiltOperat As Range Dim Scell As Range Dim Wkname As String ThisWorkbook.Application.ScreenUpdating = False iLastOp = Range("C65536").End(xlUp).Row Set wks = ThisWorkbook.Worksheets("Source") wks.Range("K:K").ClearContents wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1" _ ), Unique:=True iLastOp = wks.Range("K65536").End(xlUp).Row Set rngOperat = Range("K2:K" & iLastOp) For Each rngOperat In rngOperat ThisWorkbook.Worksheets.Add Worksheets(1).Name = rngOperat Next rngOperat Set rngFiltOperat = wks.Range("K2:K" & iLastOp) wks.Select wks.Columns("C:C").AutoFilter For Each rngFiltOperat In rngFiltOperat wks.Select wks.Range("C65536").End(xlUp).Select With Selection .AutoFilter Field:=1, Criteria1:=rngFiltOperat End With Wkname = rngFiltOperat Set wksdest = ThisWorkbook.Worksheets(Wkname) Cells.Copy wksdest.Paste wksdest.Range("K:K").ClearContents Next rngFiltOperat wks.Select wks.Range("C:C").AutoFilter wks.Range("K:K").ClearContents ThisWorkbook.Application.ScreenUpdating = True End Sub "kjp55" wrote: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to import data based on a variable
The only reason why it would stop is if the destination cell in my example
K1, is not empty. Make sure the K column is clear of any data or change it to a column where there will never be any data. Range("Z1"..... One more thing: I did not use the col1...Col8 reference I modified the example so that the headers would be: date job operator ordered start # stop # run var -+ var % "kjp" wrote: Thanks. I added the sub to the worksheet and got a compilation error all highlighted in red when I ran the compiler)... [this line is highlighted as incorrect by the compiler] wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1" _ ), Unique:=True ..... seems this statement is not valid. As I know little about Macros and subroutines, I'm not sure what I'm looking at. Do I need to change something to make it work? "Michael" wrote in message ... You will not be able to find a formula, but I build a subroutine that you can use to get you started. it assumes 9 colums, I used your example as a reference. The only thing I added was naming the sheet where the base data is as "Source". Cheers, Sub Macro1() Dim iLastOp As Integer Dim wks As Worksheet Dim wksdest As Worksheet Dim rngOperat As Range Dim rngFiltOperat As Range Dim Scell As Range Dim Wkname As String ThisWorkbook.Application.ScreenUpdating = False iLastOp = Range("C65536").End(xlUp).Row Set wks = ThisWorkbook.Worksheets("Source") wks.Range("K:K").ClearContents wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1" _ ), Unique:=True iLastOp = wks.Range("K65536").End(xlUp).Row Set rngOperat = Range("K2:K" & iLastOp) For Each rngOperat In rngOperat ThisWorkbook.Worksheets.Add Worksheets(1).Name = rngOperat Next rngOperat Set rngFiltOperat = wks.Range("K2:K" & iLastOp) wks.Select wks.Columns("C:C").AutoFilter For Each rngFiltOperat In rngFiltOperat wks.Select wks.Range("C65536").End(xlUp).Select With Selection .AutoFilter Field:=1, Criteria1:=rngFiltOperat End With Wkname = rngFiltOperat Set wksdest = ThisWorkbook.Worksheets(Wkname) Cells.Copy wksdest.Paste wksdest.Range("K:K").ClearContents Next rngFiltOperat wks.Select wks.Range("C:C").AutoFilter wks.Range("K:K").ClearContents ThisWorkbook.Application.ScreenUpdating = True End Sub "kjp55" wrote: I have a basic spreadsheet and need to import data from that sheet into a new sheet, based on criteria from one colum in the original sheet. Can't figure out a formula for this. Sheet One (Printing press waste calculator) col1 col2 col3 col4 col5 col6 col7 col8 col9 date job operator ordered start # stop # run var -+ var % 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Dan 5,000 415250 420450 5200 200 4% 6-6-06 test Ed 5,000 415250 420450 5200 200 4% 6-6-06 test Sam 5,000 415250 420450 5200 200 4% I need a new sheet for each operator, importing all rows of data, but only by the operator's name. So, the resulting worksheet will show everything (like above) but will only show data for a particular operator. I need the new sheet to import all data |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
| import data with macro | Excel Discussion (Misc queries) | |||
| import data using macro | Excel Discussion (Misc queries) | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
Linear Mode
