+ Reply to Thread
Results 1 to 4 of 4

Dynamic Data Validation

  1. #1
    Registered User
    Join Date
    01-06-2017
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Dynamic Data Validation

    I'm trying to make the list on the Production sheet that updates based on the three check boxes on the PM sheet. I currently have it working, but it doesn't exclude blank spaces when switching between options. I've tried a few different methods including making a formula with IF and AND stipulations, but it's too long to fit in the data validation bar, even when giving each scenario a shortened list name. I'll list the formula I tried, but was too long, below.

    =IF(AND(PM!D3,PM!D4,PM!D5),Task2,IF(AND(PM!D3,NOT(PM!D4),NOT(PM!D5)),Task3,IF(AND(PM!D4,NOT(PM!D3),NOT(PM!D5)),Task4,IF(AND(PM!D5,NOT(PM!D3),NOT(PM!D4)),Task5,IF(AND(PM!D3,PM!D4,NOT(PM!D5)),Task6,IF(AND(PM!D3,PM!D5,NOT(PM!D4)),Task7,IF(AND(PM!D4,PM!D5,NOT(PM!D3)),Task8)))))

    Also, I have the Tasks generate on the PM sheet based on the check boxes selected, but I had to use a ton of nested IF formulas. What I would like is to have the General Tasks move up or down depending on how long the list of Production Tasks is without having to go through the same process of making nested formulas for each line based on which boxes are checked.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,335

    Re: Dynamic Data Validation

    OMG, but you have me confused!!! Where do you want the formula? Where is the data to come from? What are the expected results?
    Glenn



  3. #3
    Registered User
    Join Date
    01-06-2017
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Re: Dynamic Data Validation

    The three check boxes on the PM page generate three different lists:

    Training: Training-Rec/Loan
    Training-Lease

    Processing:
    Loan Level 1-3
    Lease Level 1-3
    Rec Finance
    FAS
    eContract

    Support:
    Research
    Research Shared
    Rebook Step 2
    Rebook Step 4 and so on

    I want the list in the table on Production to update based on the check boxes on the PM sheet. So if Training and Processing are selected, the list in the table on Production will reflect the options available while not having any blank spaces in the updated drop down list. The lists on the Production sheet are just lists of all different combinations of selected options from the check boxes-I added them there but I will either delete them or make the text the same color as the background once I get the drop down working. I just added them while I was saving lists using Alt+F3 to put in the formula that I included in my original post. Sorry if I'm not being clear, I can upload screen shots if that will help.


    Basically, the lists on the Production page are the results I want from Data Validation in the table on that same page. I want the results to change based on the corresponding check box(es) on the PM sheet.
    Last edited by Phonix; 01-29-2017 at 10:31 PM.

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,863

    Re: Dynamic Data Validation

    While going through your files. Advice to remove all merge cell & accordingly change range in name manager & re-attach your file.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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. If statement based data validation? Dynamic data validation?
    By bjohnsonac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2014, 02:12 PM
  2. [SOLVED] Dynamic Data Validation
    By sinspawn56 in forum Excel General
    Replies: 2
    Last Post: 08-06-2012, 08:19 AM
  3. [SOLVED] Dynamic Data Validation
    By marreco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2012, 07:57 PM
  4. Dynamic Data Validation
    By Badvgood in forum Excel General
    Replies: 3
    Last Post: 12-19-2011, 07:44 PM
  5. Dynamic data validation
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-17-2011, 12:54 PM
  6. Dynamic Data Validation
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2009, 12:01 PM
  7. Dynamic Data Validation
    By taleb in forum Excel General
    Replies: 1
    Last Post: 09-13-2008, 08:08 AM

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