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

riyajusg

New Member
Joined
Mar 18, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

1616155816670.png


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
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top