+ Reply to Thread
Results 1 to 7 of 7

Using Drop downs to act as switches

  1. #1
    Registered User
    Join Date
    01-01-2014
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    7

    Using Drop downs to act as switches

    Dear All,

    I need assistance regarding the use of drop-down menus acting as switches to switch between data tables from other tabs within the same spreadsheet.

    I hope this is clear. If not, here is an example of what I am after.

    1. I have several drop-down menus set up as well as numerous tabs worth of data.

    2. Using these I'd like to call data from different tabs. i.e Option A brings a table from sheet2 and is placed in sheet1 or Option B brings a table from sheet3 and is placed in sheet1

    Any help would be amazing.

    Steve

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Drop downs to act as switches

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-01-2014
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using Drop downs to act as switches

    Hi there,

    I have attached the workbook.

    I'm not sure if I explained myself fully first time around but to get the ball rolling I would like help on the following:

    In the workbook I'd like to click on the switch (B2) and if 'FE P' is selected, the table from 'table 1' is placed in 'switch model' below the switch somewhere. Alternatively, I'd like to select 'NW' and have the table from 'table 2' brought into it's place.


    On top of this, is it at all possible to create chains of switches, i.e. once the first switch is selected (B2), is it possible to create another switch (D2) that changes the values in the worksheet from numbers to currency?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-01-2014
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using Drop downs to act as switches

    currency figures are in table 3

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Drop downs to act as switches

    We have used named ranges and INDIRECT formulas to select a range of cells that match the "name" selected B2.

    1) I highlighted each of the tables (not the title row) on each sheet, then typed a "name" into the Name Box (just to the left of the formula bar) that matches the values you are entering in B2. FE_P now has an underline in it since named ranges can't have spaces in the names.

    2) Formulas were entered into your switch model to INDEX the correct table, then display the correct row/column for each cell of the table.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-01-2014
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using Drop downs to act as switches

    Many apologies for the prolonged reply, JBeaucaire.

    Thank you ever so much for your assistance. You're an absolute life saver.

    All the best,

    Steve

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Drop downs to act as switches

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

+ 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. Multiple drop dependant drop downs in single cell
    By LittleJerry in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2012, 02:21 PM
  2. Replies: 2
    Last Post: 07-10-2012, 05:17 AM
  3. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM
  4. Use Drop Downs to Populate Other Drop Downs
    By fbombs in forum Excel General
    Replies: 2
    Last Post: 09-23-2009, 03:17 PM
  5. Cross-referenced drop-down menu (nested drop-downs?)
    By creativeops in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2005, 01:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1