Seeking Help with Dependent dynamic list with offset/countif formula and name manager

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?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Thanks - it's all very busy (too much data for a SAMPLE workbook, really).

Please add some clear annotations and signposting to the workbook.

There are errors in the Item_Formula:

=OFFSET(Cost_Items[[#Headers],[Items Description]],MATCH(SOW!#REF!,'Data Set'!$B:$B,0)-1,0,COUNTIF(SOW!$B:$B,SOW!#REF!),1)

Maybe fix those and then check again?

I can't help thinking that there must be an easier way to do what you seem to want. I'll have another look later.

Seems to be working once the formula has been fixed.

Let us know how you get on. Sometimes it's just another pair of eyes that you need.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.