+ Reply to Thread
Results 1 to 13 of 13

Data Validation - Drop down list to pull the whole sheet

  1. #1
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Data Validation - Drop down list to pull the whole sheet

    I am not sure if it is possible, just thought that I might ask. I understand that it is possible to implement a drop down list to track changes in number figures.

    I have a number of sheets, each represents a summary (including charts, text) of monthly report. I am wondering if it is possible to use a drop down list to to pop up the respective sheet?

    Thanks for taking the time to help !

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Data Validation - Drop down list to pull the whole sheet

    maybe you think about this?

    ddl1.jpg

    ddl2.jpg

  3. #3
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Hi Sandy666, Thank you very much for your Response.
    Not entirely. I would like to have a drop down list and the sheet changes based on the value within the selection.
    It is basically like a Hyperlink function which links you to the respective tab

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Data Validation - Drop down list to pull the whole sheet

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Here it is. For example, in the master sheet I can click the drop down list and select the respective sales rep code and the sheet will pop up with the respective info.

    Thank you.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Data Validation - Drop down list to pull the whole sheet

    a few suggestions...
    1. Consider putting ALL data on 1 sheet, it will make analysis/extract/summaries much simpler. Just add an extra column for Rep No.
    2. If you MUST keep each rep on it's own sheet, keep the tables consistent. Start them all in the same column and the same row.

    Not really sure what you mean about pull all data in, but this UDF (User Defined Function) will let you create a list of all sheet names and let you create a hyperlink to each sheet.

    1st create a range name (I called mine Sheetnanes
    Then put this in the Refers To box: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

    To get a list of sheet names, put this in a cell and copy down (started in A2)...
    =IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

    If you want to be able to click the cell and go to that worksheet, change that formula to this...
    =IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

    Note that this uses a UDF (User Defined Function) and requires that you save the file a .xlsm


    Then, in, say, A2, paste this and copy down as needed...
    =IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation - Drop down list to pull the whole sheet

    Try this worksheet event
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Quote Originally Posted by kvsrinivasamurthy View Post
    Try this worksheet event
    Please Login or Register  to view this content.
    Thank you for your response.

    Not quite sure why I tried to apply the code in the workbook.
    It doesn't allow me to run anything.
    See attached.
    Attached Images Attached Images

  9. #9
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Quote Originally Posted by FDibbins View Post
    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Thank you for taking the time to respond.

    I am a bit confused since all the sheets have different charts ( some are more than the others)

    could you please indicate the formula in the workbook please?

    Thank you in adv.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation - Drop down list to pull the whole sheet

    To apply the code to workbook,
    Right click on sheet tab of MASTER sheet -->View code
    Visual Basic window opens.
    Copy and paste the code.
    Close the window.
    When A3 value is changed code automatically runs.

  11. #11
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Am I missing something?

    It is not getting the job done. Could you please have a look?

    Thank you.
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Data Validation - Drop down list to pull the whole sheet

    I dont really see what could confuse you? I just followed my own instructions and it worked just fine
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Data Validation - Drop down list to pull the whole sheet

    Ah Thank you very much!! Now I see how it is working. my approach was slightly different since I already create a drop down list in the master sheet in cell D6.
    whenever I select from the drop down, the sheet appears in the master sheet. Would that be possible?

+ 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] Create Dynamic Validation List & Then Pull Related Data into Sheet
    By AlyKat in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-10-2015, 09:22 AM
  2. [SOLVED] Get the data from one sheet to other sheet using data validation (Drop down list)
    By Kiran Kurapati in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-20-2014, 02:00 AM
  3. Replies: 5
    Last Post: 07-22-2014, 06:29 AM
  4. [SOLVED] Creating a validation list then using a hlookup to pull info on the item in the drop down
    By pleasesmile in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2013, 01:44 PM
  5. Replies: 1
    Last Post: 05-15-2013, 01:55 PM
  6. Replies: 0
    Last Post: 05-06-2013, 08:33 AM
  7. Replies: 4
    Last Post: 05-17-2012, 04:40 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