Using first drop down list to consolidate data in second drop down list

**kyler13** · 10-22-2011, 01:57 PM

Wasn't sure how to title this, but I've scoured the internet without any success finding exactly what I need to accomplish here. This is what I'm trying to do:

In a data tab, I have a column with a list of various plan names. The adjacent column classifies the plans into either complete (C), start (S), or finish (F). On the user interface tab, I want to require the user to select a plan in a pulldown list and it can be any plan in the list. If that plan is classifed as "complete" or "finish", the secondary pulldown list next to it is blank/greyed out/not applicable. But if they select a start plan in the first pulldown list, the secondary pulldown list should only display a list of appropriate finish plans.

Originally I only had a single drop down list and I've been using an OFFSET function in data validation to dynamically provide the list of plans. I need to maintain this method (not a pre-defined range) such that I can add or remove plans in the data tab at any given time without affecting the functionality of the user interface tab in my spreadsheet.

**veejar** · 10-22-2011, 02:02 PM

Hi

Please upload a sample workbook if possible that would be better.
you will have to create all scenarios and name them
Also try using INDIRECT Function that might help.

Regards,

Veejar

**JBeaucaire** · 10-22-2011, 03:02 PM

Yes, a sample workbook with some manually mocked up examples of the results you're after would be helpful.

@Veejar: Kyler has already indicated they are using dynamic named ranges (OFFSET) to create a self-expanding list for the drop down. INDIRECT() and dynamic name ranges do not work together. FYI.

**veejar** · 10-22-2011, 03:23 PM

Originally Posted by JBeaucaire

Yes, a sample workbook with some manually mocked up examples of the results you're after would be helpful.

@Veejar: Kyler has already indicated they are using dynamic named ranges (OFFSET) to create a self-expanding list for the drop down. INDIRECT() and dynamic name ranges do not work together. FYI.

Hi Jerry,

I do agree with you but from what Kyler has described I am not so sure whether they have actually named all ranges. Guess if we do get a sample worbook it might be quicker to figure out the problem

**kyler13** · 10-22-2011, 05:22 PM

Wow! Quick response. Attached is a sample of what I'm trying to do. I put the desired conditions in the TopSheet tab with the referenced data in the Data tab. Thanks in advance!

Edit: And just for some background info, I currently have both drop down list cells using the dynamic range with OFFSET. The data as you see it in the Data tab is a sample of how I retrieve it so I'd like to make it work around this format. I know there's some Data Validation methods but that requires subgrouping data which reduces the automation of the workbook.

**veejar** · 10-23-2011, 01:18 AM

Hi

See the attached sheet and let me know if it works for you

Regards,

Veejar

**JBeaucaire** · 10-23-2011, 03:25 PM

Using just a "sorted list" on the data sheet, this is how I do it. We've added some more named ranges, and one Named Formula (Plans2) for use in the second drop down.