Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the filter value to look up other info
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the filter value to look up other info
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the filter value to look up other info
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the filter value to look up other info
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,OFFSE T(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 "Hugues" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reflecting info between an excel file and a word one or two excel file. | Excel Worksheet Functions | |||
Reflecting info between an excel file and a word one or two excel file. | Links and Linking in Excel | |||
Reflecting info between an excel file and a word one or two excel file. | Excel Discussion (Misc queries) | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |