+ Reply to Thread
Results 1 to 15 of 15

Returning the values of filtered items on another sheet

  1. #1
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Returning the values of filtered items on another sheet

    So I have a sheet - say for example, like this:

    Please Login or Register  to view this content.
    Is it possible to have a table of formulas, which will show me all the values in the second column of each country (e.g. Fiji)?

    The end result will be like this - I'm hoping to be able to do it so I don't need to manually filter and copy rows each month.
    Please Login or Register  to view this content.

  2. #2
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    In my head I'm thinking it might be possible to do this with an initial lookup, and then subsequent rows with an index that starts at Motor Vechicle Toyota Hilux for team leader in North to continue with a second lookup. Clunky, but is there a better way for this?

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    See this example:

    http://www.excelforum.com/excel-form...ell-value.html
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    Quote Originally Posted by Tony Valko View Post
    Thanks Tony, I don't understand with your example, the use of column E- for me I want to return the text in column B if column A = "Fiji".

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    Try this...


    Data Range
    A
    B
    C
    D
    E
    2
    Am Samoa
    Manchester tanks 10*210kgs
    -----
    Fiji
    Motor Vechicle Toyota
    3
    Am Samoa
    9kg & 4.5kg cylinders Apr order
    Time Clock- need to be expense
    4
    Am Samoa
    Mooring Bollard Part2
    12kg cylinders*588
    5
    Fiji
    Motor Vechicle Toyota
    6
    Samoa
    Apia terminal
    7
    Samoa
    Cylinder Piercer
    8
    Solomons
    New IT SERVER
    9
    Solomons
    New S&I Office Extension - WIP
    10
    Solomons
    Solomons Pacific shipping hoses
    11
    Tonga
    Stairways on tanks
    12
    Tonga
    Air & gas cylinder filling scales
    13
    Vanuatu
    ISO tank upgrade
    14
    PNG
    Pacific shipping hoses
    15
    PNG
    Feb'12 Cylinders orders + valves
    16
    Vietnam
    12kg & 45kg cylinders
    17
    Am Samoa
    Radio Equipment Aua Terminal
    18
    Am Samoa
    Tafuna Warehouse
    19
    Fiji
    Time Clock- need to be expense
    20
    Fiji
    12kg cylinders*588


    This array formula** entered in E2 and copied down until you get blanks:

    =IFERROR(INDEX(B:B,SMALL(IF(A$2:A$20=D$2,ROW(A$2:A$20)),ROWS(E$2:E2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  6. #6
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    I'm not sure if we're on the same page... I want to extract the rows which are Fiji (or Am Samoa, or PNG, doesn't really matter) into a table, the Fiji label will be a lookup (say D2) so I want the formula, which will be in cells E1-E20, to populate with whatever is in country D2.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    Hmmm...

    That's what the formula is doing except that it's not repeating Fiji (or whatever) for each row of data.

    We already know the data is related to Fiji so why repeat it for each row of data?

  8. #8
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    Can't get it working

    FWIW - columns D and E in your example are on another worksheet.

    For me, D1 is a fixed cell for the worksheet, and column E is what I am wanting the array/formula to generate for me. There's a sheet for each location (Am Samoa, Cooks, PNG etc).

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    Can you post a SMALL file that shows how/where your data is and what result you expect?

    A SMALL file is about 20 rows worth of data. We just need an accurate representative sample of what you have and what you want. Files with rows/columns full of irrelevant data just make understanding the problem more difficult.

  10. #10
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    Quote Originally Posted by Tony Valko View Post
    Can you post a SMALL file that shows how/where your data is and what result you expect?

    A SMALL file is about 20 rows worth of data. We just need an accurate representative sample of what you have and what you want. Files with rows/columns full of irrelevant data just make understanding the problem more difficult.
    Was just prepping the file

    So in the "Am Samoa" sheet, I want the formula to populate in cells C31:C38, from "A - Data" columns A and C.

    Asiapac.xlsx
    Attached Files Attached Files
    Last edited by tangcla; 11-11-2013 at 09:56 PM.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    Sorry, but I can't download files >50kb.

  12. #12
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    File above updated.
    Had to remove all formatting to get it to be under 50KB

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    Try this...

    On the Am Samoa sheet...

    Enter this array formula** in C31:

    =IFERROR(INDEX('A - Data'!C:C,SMALL(IF('A - Data'!A$3:A$26=C$1,ROW('A - Data'!A$3:A$26)),ROWS(C$31:C31))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down to C38.

  14. #14
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: Returning the values of filtered items on another sheet

    Quote Originally Posted by Tony Valko View Post
    Try this...

    On the Am Samoa sheet...

    Enter this array formula** in C31:

    =IFERROR(INDEX('A - Data'!C:C,SMALL(IF('A - Data'!A$3:A$26=C$1,ROW('A - Data'!A$3:A$26)),ROWS(C$31:C31))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down to C38.
    Thanks! Perfect, + rep.

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning the values of filtered items on another sheet

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index/Match/Large Duplication of items; not returning true values
    By Darren_Rix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2013, 07:27 AM
  2. Filter sheet then assign values into array based on this filtered sheet
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2011, 10:32 AM
  3. Conditional formatting referencing another sheet and returning multiple column items
    By Bundleodaisies in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-29-2010, 04:12 PM
  4. Conditional formatting referencing another sheet and returning multiple column items
    By Bundleodaisies in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2010, 10:04 PM
  5. Replies: 2
    Last Post: 12-15-2006, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1