+ Reply to Thread
Results 1 to 10 of 10

Drop down list that will show an entire table, is it possible?

  1. 04-25-2017, 02:06 AM #1
    JoeTrebble is offline
    Registered User
    Join Date
    04-25-2017
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    5

    Question Drop down list that will show an entire table, is it possible?

    Hi all,

    I've been tasked with simplifying the way the office uses our pricing tool, which currently sits as around 15 sheets in an excel workbook all with around 2/3 different tables on. I was wondering if it would be possible to create a drop-down tab that contains the titles of all the tables, and when selected brings down the entire table of contents, not just one cell of data. If you have any ideas on how to do this or know that it's just outright not possible, it would be greatly appreciated!

    Thanks!

    Joe
    Reply With QuoteRegister To Reply
  2. 04-25-2017, 01:23 PM #2
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Drop down list that will show an entire table, is it possible?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  3. 04-25-2017, 01:39 PM #3
    ImranBhatti is offline
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,785

    Re: Drop down list that will show an entire table, is it possible?

    Please see the attachement. if you want this then I will elaborate it here.

    drop down is in E14 and G14:H21 will change accordingly.
    Is this what you need?
    Attached Files Attached Files
    Teach me Excel VBA
    Reply With QuoteRegister To Reply
  4. 04-26-2017, 01:29 AM #4
    JoeTrebble is offline
    Registered User
    Join Date
    04-25-2017
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    5

    Smile Re: Drop down list that will show an entire table, is it possible?

    Hi Imran,

    Yes that looks great, exactly what I need! Is it possible to make the drop down on a different sheet to the tables and if so can they vary in size? (Some have more columns/rows than others). I understand the entering the INDIRECT formula as an array, and using the data validation for the boxes, but whenever I try to replicate your doings, it only pastes the first number of the tables, what am I doing wrong?

    Thanks for your help so far, that's some fine excel wizardry :o)

    Joe
    Last edited by JoeTrebble; 04-26-2017 at 04:47 AM.
    Reply With QuoteRegister To Reply
  5. 04-26-2017, 04:47 AM #5
    JoeTrebble is offline
    Registered User
    Join Date
    04-25-2017
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    5

    Re: Drop down list that will show an entire table, is it possible?

    Hi Imran,

    Yes that looks great, exactly what I need! Is it possible to make the drop down on a different sheet to the tables and if so can they vary in size? (Some have more columns/rows than others). I understand the entering the INDIRECT formula as an array, and using the data validation for the boxes, but whenever I try to replicate your doings, it only pastes the first number of the tables, what am I doing wrong?

    Thanks for your help so far, that's some fine excel wizardry :o)

    Joe
    Reply With QuoteRegister To Reply
  6. 04-26-2017, 04:56 AM #6
    ImranBhatti is offline
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,785

    Re: Drop down list that will show an entire table, is it possible?

    Sorry if you are waiting my reply.At the moment I am at office .can review it after 6 PM my time. if it suits you.
    Reply With QuoteRegister To Reply
  7. 04-26-2017, 04:57 AM #7
    JoeTrebble is offline
    Registered User
    Join Date
    04-25-2017
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    5

    Re: Drop down list that will show an entire table, is it possible?

    Anytime that works for you, we are on roughly the same time zone so it suits me, thanks!
    Reply With QuoteRegister To Reply
  8. 05-01-2017, 09:18 AM #8
    ImranBhatti is offline
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,785

    Re: Drop down list that will show an entire table, is it possible?

    Hi
    Please follow the link bellow. I hope you will be able to replicate the solution as per your need.The INDIRECT formula wil remain the same.This thread will help you to dynamically update your tables and named ranges.
    https://www.excelforum.com/excel-pro...ml#post4642749

    Please let us know if you were able to adopt it.

    Best Regards
    Imran Bhatti
    Reply With QuoteRegister To Reply
  9. 05-02-2017, 03:28 AM #9
    JoeTrebble is offline
    Registered User
    Join Date
    04-25-2017
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    5

    Re: Drop down list that will show an entire table, is it possible?

    Hi Imran,

    I greatly appreciate your help. Unfortunately when trying to replicate the process, whenever I create a dynamic table using the INDIRECT formula, and try to link it with the tables/tablelist, it only updates each slot to contain the first value of the table across all cells. Is there any way to fix this?

    Also in terms of the posted thread, I have tried to use JBeaucaire's VBA code but not sure how/when to implement it into the process.

    Thank you for your time,

    Kind Regards,
    J.Trebble
    Reply With QuoteRegister To Reply
  10. 05-02-2017, 01:08 PM #10
    ImranBhatti is offline
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,785

    Re: Drop down list that will show an entire table, is it possible?

    How exactly are your original tables formatted? (Do they have some calculated fields?, and do they have some blank cells?)
    is the number of tables fixed or they will increase.
    Can you upload another sample workbook that should mirror your original data truly?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filter table based on drop down list and show on another sheet
    By ajupillai in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2016, 04:50 PM
  2. Listbox_Click then List to show entire sheet information in Userform
    By KumarPK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2016, 03:25 AM
  3. Show items in pivot table filter's drop-down list based on other filters of the same table
    By qwertyu_zxc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  4. [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
  5. Excel Drop Down Menu - Select List Item - (Show Data Associated With That List Selection.)
    By meppley7 in forum Excel General
    Replies: 5
    Last Post: 04-11-2014, 03:13 PM
  6. [SOLVED] Select from drop down list to show another drop down list in a different cel
    By ExcelRanger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2012, 04:27 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1