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?

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?

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

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?

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?

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?

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?