A formula to filter data and copy it from one worksheet to another


G

Guest

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?
 
Ad

Advertisements

G

Guest

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.
 
Ad

Advertisements

B

Biff

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top