+ Reply to Thread
Results 1 to 8 of 8

Extract Data based on a Drop-Down List selection

  1. #1
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Extract Data based on a Drop-Down List selection

    Sheet 6 :-

    In A2 i can select a Vessel name from the drop down list (A2) this in turn in cell A6 and below returns All the species relevante to that vessel aswell as the presentation.

    At the moment it returns the species name a number of times based on how many grades of the species are lsited. (I.E if there are 6 grades of a species it returns the specie name 6 times etc)

    Is there an adjustment that can be made to just get the specie name to return once ? and only return multiple results if presentation GH and WF is relevante (I.E Cod GH, Cod WF)

    The source of all the data comes from sheet named "DATA"
    Attached Files Attached Files
    Last edited by Dom.Knight; 05-18-2021 at 05:30 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Extract Data based on a Drop-Down List selection

    This proposal adds a helper column (U) on the data sheet which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =IF(A2=Sheet6!A$2,COUNTIFS(A$2:A2,A2,B$2:B2,B2,F$2:F2,F2),"")
    Column A on Sheet6 is populated using: =IFERROR(INDEX(Data!P$2:P$400,AGGREGATE(15,6,(ROW(Data!U$2:U$400)-ROW(Data!U$1))/(Data!$U$2:$U$400=1),ROWS($A$6:$A6))),"")
    Column B on Sheet6 is populated using: =IFERROR(INDEX(Data!F$2:F$400,AGGREGATE(15,6,(ROW(Data!V$2:V$400)-ROW(Data!V$1))/(Data!$U$2:$U$400=1),ROWS($A$6:$A6))),"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Extract Data based on a Drop-Down List selection

    JeteMc - Thank you very much for that. I have another question you maybe ablt to assist with this sheet.

    In Column J, Sheet6, In J6 i have the heading salenote is it possible to have the total kilos for each specie/ presentation to match the results you have already created to match the selection of the vessel.

    If you select the vessel tab you will notice in Row 104 the results are already there for reference.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Extract Data based on a Drop-Down List selection

    Try pasting the following into cell J6 and then double clicking the fill handle so that it will copy down to cell J46:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I suggest that cell A2 on Sheet6 should be populated using =Vessel!A2 instead of a drop down to insure the two spreadsheets stay in sync.
    Let us know if you have any questions.

  5. #5
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Extract Data based on a Drop-Down List selection

    JeteMc -Thanks for the reply. I apprecite where your coming from with using Vessel to keep the data in sync however do you have a formuale that could use the information from Data instead ?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Extract Data based on a Drop-Down List selection

    Try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  7. #7
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Extract Data based on a Drop-Down List selection

    JeteMc - Thanks for the assistance both formulaes work i prefer using the infortion already collected from Data.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Extract Data based on a Drop-Down List selection

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Multiples Index/Match/Multiples Columns But to be concatenate in respective Rows
    By AndyJr in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 12-09-2018, 11:21 PM
  2. Replies: 3
    Last Post: 04-21-2017, 05:32 PM
  3. [SOLVED] vba to concatenate data and return multiples values from unique list
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-30-2017, 11:27 AM
  4. [SOLVED] Need Index and Match to return multiples values in the same cell
    By ludi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2013, 10:50 AM
  5. [SOLVED] Excel macro to return Current Time in multiples of 5 minutes
    By panditji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2012, 03:10 PM
  6. Search multiples and return unique value
    By pleiadeez7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2011, 09:59 AM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 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