Using the filter value to look up other info

[Hugues]

Good afternoon,
I have a spreadsheet that I use for Forecasting. Columns include:
customer(text), item(text), item desc(text), current stock(number), unit
sales by past months (number), and unit forecast by future month(formulas). I
filter by item to subtotal unit sales by month and forecast while seeing all
customers who buy that item. so far so good.
Here's my problem: once the data is filtered, under the subtotals, I want to
see existing purchase orders and their ETA dates for that same filtered item.
This ordering data comes from another worksheet. How can I isolate the
filtered item(text) to create a vlookup? Every time I change the filter, the
cell rows that are shown change...
Can this be done?
Thank you very much,
Hugues

 

[Art Farrell]

Hi,

My interpretation may be incorrect but I would do this.

With the second sheet being Sheet2 insert a column before Column A and fill
down
a series starting in A1 from 1 to ? to match the Row numbers. Then on
sheet1 use
a formula starting in Row 2 of Column H(?) and drag down:

=VLOOKUP(ROW(),Sheet2!$A$1:$D$100,3)

where your P.O. information is now in Column C on sheet2. When you use the
filter the
results of the filter rows will match those on sheet2.

CHORDially,
Art Farrell

 

[Hugues]

Good Monday morning Art,

Thank you so much for taking the time to read, think about and reply to my
post. Your solution would surely work if my explanation had been clearer, but
then again, it's a very complicated Excel file that I tried to explain in 2
short paragraphs.

Here's a little more info about our company and what I need:

Our company sells shower curtains and bathroom accessories (Tumblers, Soap
Dishes, Toothbrush Holders, etc).

My worksheet, when filtered by item, shows how much we've sold of a given
product to every customer who buys it, and SUMS UP (through a subtotal - eg
=SUBTOTAL(9,T3:T1671)) the forecast for all customers.

Example: when I filter my sheet by our item "30LINER/BGBEISPL", it shows
that 9 different customers buy our BASIC BEIGE LINER. All 9 customer names
show up in Column A with the FILTERED item number "30LINER/BGBEISPL" right
next to it in Column B.

What I need is a way to "subtotal" or "return" or "extract" the filtered
item number ("30LINER/BGBEISPL") so that I can VLOOKUP information about that
item from another sheet.

If this isn't clearer, thanks again for trying. I'll figure it out somehow
eventually...

Best regards,
Hugues

 

[Art Farrell]

Hi Hugues,

Try this formula courtesy of Jason Morin about a year ago. Put the formula
in some cell where you want to collect your item description:

=INDEX(B1:B2000,MAX(ROW(B1:B2000)*SUBTOTAL(3,OFFSET(B1:B2000,ROW(B1:B2000)-M
IN(ROW(B1:B2000)),,1))))

This is an array formula so it must be entered with CTRL-SHIFT-ENTER.

If you are careful in copying it ( I wasn't and couldn't make it work at
first), it should give you the result you want.

CHORDially,
Art Farrell