+ Reply to Thread
Results 1 to 7 of 7

Data Validation Cell based on previous Data Validation Cell?

  1. #1
    Registered User
    Join Date
    01-14-2012
    Location
    Longmont, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    22

    Data Validation Cell based on previous Data Validation Cell?

    There is probably an easier way to do this, but this is what I've got:

    I created an evaluation spreadsheet with 4 sheets (Excel 2013).

    Sheet 1: The Evaluation
    Sheet 2: The list of Names and answers for data validation on sheet 1
    Sheet 3: Raw data that would always get updated with a copy/paste that has Names and numbers
    Sheet 4: A Pivot Table for each name in Sheet 3 for easy view of numbers per name

    The Evaluation sheet B3 has data validation so a drop down can select the desired name.
    What I am wanting to do is somehow have the C3 cell have a drop down so it will show all numbers for the name that was selected in cell B3 (based on the numbers in Sheet 3).
    The purpose of attempting it this way, is that each day the pivots would be refreshed to populate the new numbers for names. They would be sorted newest to oldest, so the newest ones would always show first.

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Dessesbo; 04-14-2015 at 07:12 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Data Validation Cell based on previous Data Validation Cell?

    Hello,

    what you describe is called dependent data validation. You already have almost everything set up. What you now need to do is:

    Create named ranges for each column in the pivot table

    Name1 =Pivots!$A$2:$A$7
    Name2 =Pivots!$B$2:$B$7
    Name3 =Pivots!$C$2:$C$7
    Name4 =Pivots!$D$2:$D$7
    Name5 =Pivots!$E$2:$E$7

    The order of the names in the pivot table need to remain exactly the same, otherwise this will return wrong numbers in the dropdowns.

    Then on the Evaluation sheet, use data validation in cell C3 with the "List" option and let it refer to

    =indirect(B3)

    No $ signs!

    Read more about dependent data validation on the Contextures site.

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Data Validation Cell based on previous Data Validation Cell?

    Try this and see if it's what you want.
    Attached Files Attached Files
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Registered User
    Join Date
    01-14-2012
    Location
    Longmont, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Data Validation Cell based on previous Data Validation Cell?

    In the end, the pivot table will have hundreds of numbers.. so isn't there some type of A:A instead of Name1=Pivots!$A$2:$A$7?

  5. #5
    Registered User
    Join Date
    01-14-2012
    Location
    Longmont, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Data Validation Cell based on previous Data Validation Cell?

    Pretty Amazing, dude... does exactly what I want. However, I have to reverse code what you did in order for this to work... Meaning, I'm playing off your sheet and not really knowing how I'm doing what I'm doing.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Data Validation Cell based on previous Data Validation Cell?

    Quote Originally Posted by Dessesbo View Post
    Pretty Amazing, dude... does exactly what I want. However, I have to reverse code what you did in order for this to work... Meaning, I'm playing off your sheet and not really knowing how I'm doing what I'm doing.
    If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

    If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Data Validation Cell based on previous Data Validation Cell?

    Quote Originally Posted by Dessesbo View Post
    In the end, the pivot table will have hundreds of numbers.. so isn't there some type of A:A instead of Name1=Pivots!$A$2:$A$7?
    That approach shows the priciple. You can always use dynamic range names instead of fixed references. They will grow and shrink with the data.

+ 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] Data Validation: How to clear/delete the content of the cell and not Data Validation List?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2015, 09:42 AM
  2. Replies: 10
    Last Post: 12-19-2014, 07:14 PM
  3. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  4. [SOLVED] Data validation - limit based on previous choice
    By Ricardo Mass in forum Excel General
    Replies: 8
    Last Post: 09-25-2013, 08:56 AM
  5. data validation dependant on previous cell
    By joeloyzaga in forum Excel General
    Replies: 4
    Last Post: 09-22-2013, 10:26 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