+ Reply to Thread
Results 1 to 7 of 7

Extract entire data from drop-down list

  1. #1
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    147

    Extract entire data from drop-down list

    Hi All,

    I have a big data in a 2 worksheets & what i want to do is once data selected from the worksheet Selections! the drop-down list which is either COMPANY A/COMPANY B, the entire data is extracted & can be displayed from List!A4:BH128. I have tried using INDEX & MATCH but no luck.
    Please find the attachment.

    Your help is very much appreciated.
    Thank-You very much!
    Attached Files Attached Files
    Last edited by chaiyya345; 01-05-2020 at 10:23 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    518

    Re: Extract entire data from drop-down list

    You direct can run filter at sheet(List) columns(AC) or use VBA to get you want.

  3. #3
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    147

    Re: Extract entire data from drop-down list

    Hi ikboy
    i try as much possible not to use direct filter due to not to interrupt with the source data & also avoid using VBA, i believe there is a way for just using functions, its just i still get no luck for this matter

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,892

    Re: Extract entire data from drop-down list

    Try pasting the following into cell A4 on the Selections sheet then copy over and down until about row 130:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the formula is set up to extract data down as far as row 200 on the List sheet. If the actual data extends further than row 200 change the ranges in the formula.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    147

    Re: Extract entire data from drop-down list

    Hi JeteMC,

    Yes it works! Btw, could you edit the formula so that the 0 are not displayed? Otherwise, i could just use the custom formatting 0;-0;;@


    Will mark this thread to SOLVED & added reputation to you
    Thank-You very much!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,892

    Re: Extract entire data from drop-down list

    You're Welcome, Thank You for the feedback and for marking the thread as 'Solved'.
    As to the zeroes, I would suggest using the custom formatting, or conditional formatting Cell Value = 0, rather than adding an extra calculation in the formula for resource management. In fact if the spreadsheet becomes sluggish you might consider using the above formula for columns A:B (as there are no codes on the List sheet) only. You could then use the following for column C and to the right:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I hope that you have a blessed day.
    Let us know if you have any questions.

  7. #7
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    147

    Re: Extract entire data from drop-down list

    Hi JeteMC,
    I think ill just use the custom formatting for this matter & just use your formula
    HTML Code: 
    =IFERROR(INDEX(List!A$4:A$200,AGGREGATE(15,6,(ROW($A$4:$A$200)-ROW($A$3))/(List!$AC$4:$AC$200=$C$1),ROWS($A$1:$A1))),"")
    to every cells.

    Thank-You very much

+ 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 or Lookup function based on 2 drop down list to extract data from one sheet to anoth
    By FrancisM2411 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-20-2018, 12:43 PM
  2. Replies: 9
    Last Post: 05-02-2017, 01:08 PM
  3. [SOLVED] How to use drop down list to return entire table of data?
    By KTBFFH in forum Excel General
    Replies: 10
    Last Post: 06-26-2015, 05:55 PM
  4. Extract Data from one Workbook to another based on a drop down list
    By J$wizzy1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2014, 04:05 PM
  5. [SOLVED] Extract entire rows from list based on criteria
    By Test123Test in forum Excel General
    Replies: 5
    Last Post: 04-29-2013, 09:28 AM
  6. Extract data from sheet selected from drop down list
    By kanight in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2011, 07:20 AM
  7. How to view entire drop down list in Excel 2007 (Win 7)
    By Sugartit in forum Excel General
    Replies: 3
    Last Post: 02-16-2011, 03:38 AM

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