Dependent dynamic list with offset/countif formula and name manager

[juliecooper255]

Hi,

I hope someone can help me figure out what is going wrong with my dependent dropdown. I have tried to give all the details below:

1- Data

Column A = Headers
Column B = Subheaders
Column C = Items

2- Dropdown list 1 = Headers (C16, then copied down all lines)
Since my data is in a tabular format, I created a dynamic array using the formula =UNIQUE(FILTER(A:A,(A:A<>"")*(A:A<>"Headers")))
Then, to create my first dropdown, I use the data validation box pointing at the dynamic array using the formula ='Cost Items'!$M$2# as the source
Then copy the cell with the dropdown list down my spreadsheet to be usable for each line (Column C)
All works great.

3- Second dropdown list = Subheaders (dependent on headers return above) (D16, then copied down all lines)
In cell D16, the subheader dropdown list was created using the formula =OFFSET(Cost_Items[[#Headers],[Subheaders]],MATCH(SOW!D16,'Cost Items'!A:A,0)-1,0,COUNTIF(A:A,SOW!D16),1).
As offset doesn't work with the data validation, I created a name (subheader_formula) for this formula and used the name in my data validation box, which works perfectly.

4- Third dropdown list = Dependent on the result from the Subheader dropdown list in D16
I follow the same method for this dropdown list as for cell D16, but it all falls apart here.
The formula I copied to name manager is =OFFSET(Cost_Items[[#Headers],[Items Description]],MATCH(SOW!E16,'Cost Items'!$B:$B,0)-1,0,COUNTIF($B:$B,SOW!E16),1)
This formula works perfectly in Excel cells, but for some reason, the data validation will not work with the name manager. I get an error saying: "the source currently evaluates as an error"

I can't find any reason why that would be. Both formulas in D16 and F16 are constructed the same way, so why would one work and not the other?

I made a small sample file, is there a way to share it?

 

[juliecooper255]