Dynamic Data Validation

**Phonix** · 01-29-2017, 08:50 PM

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.

**Glenn Kennedy** · 01-29-2017, 09:23 PM

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

**Phonix** · 01-29-2017, 09:40 PM

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.

**avk** · 01-30-2017, 02:57 AM

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

Dynamic Data Validation

LinkBack

Thread Tools

Rate This Thread

Display