Formula is valid but not with NAMED RANGE

Been working on this problem for 3 days, cant seem to get it right. I am working off of the general principles for dependent drop-down list from here http://www.contextures.com/xlDataVal15.html

On the Lists tab, instead of typing in each list, I thought it would be better to create a pivot table for each list that pulls from the ProgramModules Tab. For purposes of this issue, I have a pivot in Lists!B2 with data range 'ProgramModules'!$C$5:$C$400. This works...but I have to manually remove items from the list. I would rather dynamically build the range for the pivot table based upon the header in the Lists Tab for each column.

Using Column B, with Header as "ACTIVITIES", I am trying to build a dynamic formula that will a unique set of value from everything below ProgramModules!C4 where the adjacent cell in ProgramModules!B:B = Activities. I do this using
=INDEX(ProgramModules!$B:$B,MATCH(Lists!B$1,ProgramModules!$B:$B,0)):INDEX(ProgramModules!$B:$B,COUNTIF(ProgramModules!$B:$B,Lists!B1))



The First part (before the colon) should find the first cell reference instance of the Value of Lists!B1 ("Activities") in the B Column of ProgramModules. The second part should find the last cell reference.

But when I Use this formula for the pivot table range I get an error telling the " Data Source Reference is not valid".


I feel like I'm 99.9% of the way, but some syntax thing is getting the best of me.


Can someone please help!

Attached is the file I am working with.

I like to use KEY columns to give myself unique indexable values that are easy to test. I've added a formula in column D on ProgramModules that indexes the unique values in that data table into the categories in column C.

With that, we can use a simple INDEX/MATCH on the category name + the row in the list to test if there is a Backpacking-2 or not, and if there is, list the Activity from that row. On the LISTS sheet, the formula in C2 is then copied down and across to J14 to get all the values to appear. You can keep copying for as many new category columns as you add, or as far down as needed to get all the values in any one list to appear.

OK, I cry uncle. Here's a macro you can put in the LISTS sheet module that will simply put in the formula, then remove it leaving only flat values an blanks. I offer the macro solution because I see you're already using macros in the workbook. The KEY column on PM page is still used.

Please Login or Register  to view this content.

This is a sheet event macro. It does not go in a standard code module (Module1, Module2...) It goes into the SHEET module called List1.

Doubleclick List1 in the VBAProject panel on the left to open the sheet module. Remove any code from in there you don't actually want.

Paste in the code I gave you.

This macro is an "activation" macro so it triggers itself each time you bring up the LISTS sheet onscreen.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Dude, this is a never ending project then.

Why can't you setup your lists and be done with it? All this dynamic range stuff is fine in small controlled doses, but you need these lists up and running and verifiable. If you can add data to your ProgramModules, you can just as readily add them correctly to manually named ranges in the LISTS page.

Then you can use simple INDIRECT() data validation formulas.