Dependent drop down list using offset

[Shadkng]

Hi, I am trying to figure out a problem with dependent drop down lists boxes using data validation. I made the formula below following a tutorial online - I named this formula in name manager and used the name in the data validation source box. My main drop down list is in B24 and the second drop down is in D24. Those cells in the formula needs to be fixed as $B$24 - if the $ is removed to make them relative it won't work. The issue is I need the drop downs to work in the rows below. If I copy row 24 to row 25 and I choose a different item in the row 25 main drop down, then the second drop down still points to the row 24 drop down. If anyone has any ideas I would appreciate it. Thanks

=OFFSET(COLORS!$A$2,1,MATCH(QUOTE!$B$24,COSTS!$A$146:$A$253,0)-1,COUNTA(OFFSET(COLORS!$A$2,1,MATCH(QUOTE!$B$24,COSTS!$A$146:$A$253,0)-1,40,1)),1)

 

[Shadkng]

Any help on tbis question would be appreciated. Thanks