# How do we using dynamic list based on condition criteria from cell value?

#### riyajusg

##### New Member
We using dropdown dynamic list from master using OFFSET function for except blank data's,
Here, we would like to next two columns lists should allow/block based on the previous column list value, Kindly help us anyone, how to do this functions in excel?

Eg:

In above example,
First List, Should not be REMOTE LOCATION, then only the user allow to access next column lists, (OR) user should not allow to access other two columns, If Venue column list value is equal to "Remote Location" (Like red marked)
Attn: (All the Columns are Dropdown Lists - comes from Master Sheet = dynamic list using formula like =OFFSET(MASTER!\$D\$2,0,0,COUNTA(MASTER!\$D:\$D),1))

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### JamesCanale

##### Board Regular
MrExcelPlayground.xlsm
KLMNOPQRSTU
7Remote Location
8Area 1
9Area 2
10
11
12Area 1VanCompanyVanBikeCarGroupCompanyEvent
13Remote Location
14Area 2BikeEventVanBikeCarGroupCompanyEvent
15Remote Location
16Area 1CarGroupVanBikeCarGroupCompanyEvent
17Remote Location
Sheet39
Cell Formulas
RangeFormula
O12:Q12,O16:Q16,O14:Q14,O13,O15,O17O12=IF(K12<>\$K\$7,{"Van","Bike","Car"},"")
S12:U12,S16:U16,S14:U14,S13,S15,S17S12=IF(K12<>\$K\$7,{"Group","Company","Event"},"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L12:M17Expression=\$K12="Remote Location"textNO
Cells with Data Validation
CellAllowCriteria
K12:K17List=\$K\$7:\$K\$9
L12:L17List=\$O12:\$Q12
M12:M17List=\$S12:\$U12

This uses some conditional formatting to color the 'remote location' cells red, and the drop downs will contain no information to select there. It works by having the rows of lookups for the drop down based on the first selection. Those rows of lookups should be put somewhere else out of the way.

Replies
7
Views
873
Replies
2
Views
90
Replies
1
Views
172
Replies
7
Views
107
Replies
29
Views
2K

1,129,747
Messages
5,638,118
Members
417,010
Latest member
jnuss03

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back