Dependent dropdown with offset formula

[juliecooper255]

Hi everyone, I hope someone can help me figure out what I am missing...

I have 3 dependent dropdowns; the first 2 work perfectly, but the 3rd doesn't. I tested the formula outside the validation box, and it works great. The process seems to fall apart when creating the name with the name manager. The formula will return the name from the name manager instead of the list. As I am taking all the same steps as for the previous dropdown, I can't figure out where it is falling apart.

I attached a document with all my screenshots and formulas; I hope someone will know what I am doing wrong.

 

[Cubist]

You're missing an = sign. It's registering as a text string not the name of the list.

 

[juliecooper255]

You're missing an = sign. It's registering as a text string not the name of the list.

Thanks, that's when you know, it is time to get some sleep...

I fixed this, but the formula still does not work. When I open the name manager, the formula has changed again and looks odd. I have attached a new screenshot. I feel this may be the issue but I have no idea what is going on.

 

[juliecooper255]

Thanks, that's when you know, it is time to get some sleep...

I fixed this, but the formula still does not work. When I open the name manager, the formula has changed again and looks odd. I have attached a new screenshot. I feel this may be the issue but I have no idea what is going on.

Well, I added the $ to the B:B column, and the formula now stays the same which is good. Unfortunately, the drop-down still doesn't work. I am sooo close This is the last hurdle!

 

[Cubist]

I'm talking about the Source formula. It should be

=item_formula

 

[juliecooper255]

That's what I understood, and I fixed it. Unfortunately, the formula still doesn't work.

The formula I copied in the 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)

So it goes this way:

E16 = =OFFSET(Cost_Items[[#Headers],[Subheaders]],MATCH(SOW!D16,'Cost Items'!A:A,0)-1,0,COUNTIF(A:A,SOW!D16),1) this is working perfectly

F16 = =OFFSET(Cost_Items[[#Headers],[Items Description]],MATCH(SOW!$E16,'Cost Items'!$B:$B,0)-1,0,COUNTIF($B:$B,SOW!$E16),1) This is not working when copied in name manager but works when in a cell,