Returning a query on a separate spreadsheet.

[Stillanonymous]

I saw a question similar to this about a week ago, but I
tried the result and it didn't suit my situation.

I have a list of company numbers, and a list of the SKU
from the products we sell that they make. In one column is
the company number, and in another is the SKU. We have it
set up so that if a company makes more than one item, then
the company number will just be repeated as many times as
necessary while we list the SKU in the 2nd column.

Company # SKU
0001 656
0001 657
0002 458
0002 459
0002 460

I need to set up an equation that will search the first
column (there are over 800 rows) and automatically return
a list of every SKU that matches the company #. The
problem is (and where the formula didn't work) that the
list is updated regularly, so that by the end of next week
their could be 900 rows, so my equation must specify an
entire column instead of just a finite array of values, so
that it can be updated without changing the equation. Any
suggestions?

This would be for a separate spreadsheet. That is, I've
got three spreadsheets, Production, Usage, and Database,
which contains all of the data. On the Production
spreadsheet, someone simply has to enter the company
number, and it will automatically list all of the SKUs on
the Usage spreadsheet by looking up the data in Database.
I want it to be simple and user-friendly, so that ALL
someone has to to do is enter the company number, and
they'll get the info they need.

Thanks again.

 

[Stephen Dunn]

Hi Anon,

If the method given was as I suspect, then you only need to use a dynamic
range in place of the fixed range, or specify a range that covers every cell
in the column except for one.

So, for instance, if the range used in the formula is A2:B100

You can replace that with:

OFFSET($A$2,,,COUNTA(A:A)-1,2)

Or

A1:A65535

Steve D.