Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to another
I currently have one worksheet setup that contains a large inventory of
equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to another
Define: large inventory of equipment.
How many rows and how many columns? Biff "GeneR" wrote in message ... I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to another
You can use programming and an Advanced Filter. There are examples he
http://www.contextures.com/excelfiles.html Under Filters, look for 'FL0001 - Product List by Category' or 'FL0005 - Phone List for Selected Name' GeneR wrote: I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to ano
Approx. 18,000 rows and 12 - 15 columns.
Thanks "Biff" wrote: Define: large inventory of equipment. How many rows and how many columns? Biff "GeneR" wrote in message ... I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to ano
Formulas would not be the best approach. See Debra's reply.
Biff "GeneR" wrote in message ... Approx. 18,000 rows and 12 - 15 columns. Thanks "Biff" wrote: Define: large inventory of equipment. How many rows and how many columns? Biff "GeneR" wrote in message ... I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to ano
If it makes any difference, the most any one department would have on their
inventory is probably 200 items with most of the departments only having less than 75. The database can be sorted by department number. "Biff" wrote: Formulas would not be the best approach. See Debra's reply. Biff "GeneR" wrote in message ... Approx. 18,000 rows and 12 - 15 columns. Thanks "Biff" wrote: Define: large inventory of equipment. How many rows and how many columns? Biff "GeneR" wrote in message ... I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to filter data and copy it from one worksheet to ano
Ok, if your data is sorted by the dept code you can try this and see if the
performance is acceptable. THE DATA MUST BE SORTED BY THE DEPT CODE OR THIS WON'T WORK! Here's a small sample file: Filter.xls 26.5kb http://cjoint.com/?kzekHu6Yq6 The sample file uses a much smaller data table than you describe, 20 rows by 5 columns, but you'll see the formula needed. **The formula on Sheet2 B2 has been copied across to 5 columns then down to enough rows that it covers the maximum expected number of matches. For example, you say: the most any one department would have on their inventory is probably 200 items with most of the departments only having less than 75. So that means you would have to copy the formula to AT LEAST 200 rows. For those who might be following this thread but don't want to/can't download the sample file: Data table on Sheet1 in the range A2:F20. The SORTED code numbers in column A. The sort can be either ascending or descending, it doesn't matter. Sheet2 A2 = user input of code number to filter. Formula entered in Sheet2 B2: =IF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$A$2:$A$20,$ A$2),INDEX(Sheet1!$B$2:$F$20,MATCH($A$2,Sheet1!$A$ 2:$A$20,0)+ROWS($1:1)-1,COLUMNS($A:A)),"") Copied across then down (see ** above). Biff "GeneR" wrote in message ... If it makes any difference, the most any one department would have on their inventory is probably 200 items with most of the departments only having less than 75. The database can be sorted by department number. "Biff" wrote: Formulas would not be the best approach. See Debra's reply. Biff "GeneR" wrote in message ... Approx. 18,000 rows and 12 - 15 columns. Thanks "Biff" wrote: Define: large inventory of equipment. How many rows and how many columns? Biff "GeneR" wrote in message ... I currently have one worksheet setup that contains a large inventory of equipment. I want to be able to setup another worksheet for users to input a number (department code) and have it filter and copy the inventory from the large inventory. In other words, I only want them to be able to call up their individual inventories by simply inputing their code, without having to see or worry about anything else. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy from B worksheet to A worksheet with NO repeated data | Excel Discussion (Misc queries) | |||
How can i copy and paste data when there is a filter in the worksheet.... | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions |