+ Reply to Thread
Results 1 to 7 of 7

Extract data from specific table using dropdown list to select table

  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Staffordshire
    MS-Off Ver
    2016
    Posts
    3

    Extract data from specific table using dropdown list to select table

    Hi all

    I have very basic knowledge of excel, but am able to cobble formulas to get it to do what is required.

    I was tasked with putting together a costs calculator for my sales team, so they could give customers an on site budget estimate for roller blinds based on dimensions.

    What I have devised, is a list of materials available, and in that line they are able to select available dimensions (drop & width) which then displays the correct cost value from a table that particular material relates to.

    I have managed to get a match formula to work, and everyone is extremely impressed with how it works. Unfortunately, they are too impressed and now want me to adapt the cost sheet so instead of having separate lines for each material, they can select the material from a drop down menu, which will then fetch the values data from the correct table. This is so they can have multiple lines of the same material or mix and match quantities one a single cost sheet.

    I have looked and looked, but cannot figure out how to get the value from the initial drop down list to select the data from the correct table.

    Is this actually possible at all, and if so, could someone point me in the right direction regarding formulas etc.

    many thanks in advance.

    Maggie - getting older by the minute!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract data from specific table using dropdown list to select table

    I would suggest that you move the Dart & Thames table to A43, and the Roach table to A64 using CUT/Paste, so that the tables are underneath each other rather than arranged as 2 across and 2 down. Then you can add another small table to (say) T1:U7 like this:

    Table …… Row
    AMAZON ..... 1
    DART ........ 43
    MEDWAY ... 22
    ROACH ...... 64
    ROE .......... 22
    THAMES .... 43

    The Row column here is just the first row of each table.

    You might like to use Name Manager to set up the named range Cost_Table to refer to ='Dummy table'!$T$2:$U$7.

    Then your INDEX can cover the columns A to Q, and you can use a VLOOKUP on the table name to return the row (minus one) which needs to be added to the MATCH which determines the row to get the data from, as the drops are all the same for the different fabrics. The formula in I10 of the Calculator sheet would then become:

    =IFERROR(INDEX('Dummy table'!$B:$Q,MATCH($G10,'Dummy table'!$B$1:$B$15,0)+VLOOKUP(B10,Cost_table,2,0)-1,MATCH($E10,'Dummy table'!$B$2:$Q$2)),"")

    which you can copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract data from specific table using dropdown list to select table

    Actually, your tables cover rows 1 to 19 (it might be different in the hidden sheet that you actually use), so the formula would be:

    =IFERROR(INDEX('Dummy table'!$B:$Q,MATCH($G10,'Dummy table'!$B$1:$B$19,0)+VLOOKUP(B10,Cost_table,2,0)-1,MATCH($E10,'Dummy table'!$B$2:$Q$2)),"")

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    10-04-2018
    Location
    Staffordshire
    MS-Off Ver
    2016
    Posts
    3

    Re: Extract data from specific table using dropdown list to select table

    Hi Pete

    thank you for your help. However, I have tried your suggestions, and though I dont get any error messages, I also dont get any values in the price column.

    I have created a dropdown list for the fabrics, and a smaller table for the cost tables etc, but the formula doesn't seem to get calculated?

    Attached is the revised sheet with your suggestions included.

    Thanks

    Maggie
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract data from specific table using dropdown list to select table

    There are 3 things wrong.

    Firstly, the formula you are using should be this:

    =IFERROR(INDEX('Dummy table'!$B:$Q,MATCH($G10,'Dummy table'!$B$1:$B$19,0)+VLOOKUP(B10,Cost_table,2,0)-1,MATCH($E10,'Dummy table'!$B$2:$Q$2)),"")

    You had a 2 in there. Remember to copy the formula down after amending it.

    Secondly, you have not defined the named range Cost_table. You can use Name Manager on the Formulas menu to do this - select cells T2:U7 on the Dummy table sheet, then use Define Name to set this up.

    Thirdly, you have extra spaces at the end of the names in that table (all except ROACH), so you need to remove these - click in the extreme right side of the formula bar, and you will see that the cursor is not right up against the final character.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    10-04-2018
    Location
    Staffordshire
    MS-Off Ver
    2016
    Posts
    3

    Red face Re: Extract data from specific table using dropdown list to select table

    Hi Pete

    I had defined the Cost-table named range on the dummy sheet.
    Also, you suggested changing the B1 value to B2 in your original message

    However, the spaces at the end of names in the Cost-table were the issue..... I removed them and everything clicked into place!

    Many many thanks, your help is VERY much appreciated, and has possibly saved me numerous sleepless nights!

    Maybe now my bosses will PAY for me to get some excel training!

    Maggie

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extract data from specific table using dropdown list to select table

    Glad you got it working - keep pressing your bosses for the training.

    Thanks for the rep.

    Pete

+ 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. Extract data table from a specific web url
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2017, 07:16 AM
  2. Extract data table from a specific web url
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2017, 09:36 AM
  3. [SOLVED] Select and Extract a line of Data in a Table to Populate a Template Worksheet
    By DGAlamo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-20-2016, 11:30 AM
  4. How to extract data of any specific day out of pivot table.
    By mir.mudassir in forum Excel General
    Replies: 1
    Last Post: 02-02-2015, 09:44 PM
  5. Select item of a dropdown list (data validation) and refresh one pivot table
    By ATN123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2014, 07:40 AM
  6. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  7. Extract specific data from a table
    By Kristof088 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-10-2012, 09:07 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