+ Reply to Thread
Results 1 to 5 of 5

Drop down list that jumps to a cell in the same sheet

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    4

    Drop down list that jumps to a cell in the same sheet

    Hi, I am looking to create a drop down list that when I choose a value it will take me to a certain cell in the same sheet.

    For example the sheet I'm working on has building floors:
    Block A, Ground Floor, Level 1, Level 2, Level 3,
    Block B, Basement, Ground Floor, Level 1, Level 2, Level 3

    Each new level in my sheet has it's own defined name, meaning Block A -> Level 1 is called aLevel1, Block B -> Ground Floor is called bGF, etc

    I need to create a drop down menu of all the levels as there is too many to list along the excel sheet. So that the user can easily choose a level from the list, then be directed to the corresponding cell on the same sheet. Even if I can only do one drop down list per block that is fine, as in Block A has it's own drop down list, Block B a different one, etc...

    I am fine with trying to create macros and whatever anyone thinks will help me.
    Last edited by jhkgbs; 07-10-2018 at 10:14 AM. Reason: Addtional Information

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Drop down list that jumps to a cell in the same sheet

    .
    Paste in the Sheet Level Module :

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    4

    Re: Drop down list that jumps to a cell in the same sheet

    Hi, thank you for your response. Would you be able to tell me what in this code I would have to change so that it correlates with my worksheet? I have never used a ComboBox before.
    I have attached a small example of the sheet I am working with. In the example I have defined the name of the cells like I have in the actual sheet I'm working with.

    Thank you
    Attached Files Attached Files
    Last edited by jhkgbs; 07-10-2018 at 11:06 AM. Reason: updated spreadsheet example

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Drop down list that jumps to a cell in the same sheet

    .
    Try this change in the macro code :

    Please Login or Register  to view this content.
    The forum is not presently allowing me to post the sample workbook. Please download from here : https://www.amazon.com/clouddrive/sh...yqliahL2qSSz3n
    Last edited by Logit; 07-10-2018 at 02:50 PM.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Drop down list that jumps to a cell in the same sheet

    I prefer to have my applications data-driven rather than coded, so here is my take on the requirement.

    All the "dirty work" happens on the Lookup Sheets. Once you fill in the table, you can hide this sheet.

    The table has 4 columns.
    1. Block is the block name
    2. Sheet is the sheet you want the user to go to. In this example there is one sheet for the entire block, but you can have different sheets for different levels in the block
    3. Level is the level
    4. Cell is the cell you want the user to go to.

    There are three pivot tables on the Lookups Sheets. There is nothing you have to do with these. These drive the slicers on the control panel.

    The control panel is the user interface. Slicers are self-cascading meaning if you select an item in one slicer, it will limit what the other slicers display.

    Make one and only one selection in each of the slicers and click on the GO Button. You will be taken to the sheet and the cell for that combination.
    Attached Files Attached Files
    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 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] Get last n row data from Columns in another sheet based on cell value using drop-down list
    By analystbank in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-14-2017, 09:16 AM
  2. Replies: 9
    Last Post: 09-20-2015, 11:30 AM
  3. [SOLVED] VBA Code to copy cell value on another sheet on drop down list changed
    By aftermath09 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-16-2015, 12:27 AM
  4. Replies: 5
    Last Post: 09-22-2014, 05:28 AM
  5. Moving row to different sheet depending on cell value from drop down list
    By antagius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2012, 06:06 AM
  6. Replies: 0
    Last Post: 03-01-2012, 12:13 PM
  7. Replies: 5
    Last Post: 10-27-2005, 01:55 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