Dependent drop down list using offset

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
228
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)
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,108,786
Messages
5,524,865
Members
409,606
Latest member
TRINBB

This Week's Hot Topics

Top