validation in excel sheet !

Subin Thomas

i have two columns

type has a dropdown having => w,s,a

month has a dropdown having => 3,4

3 is marvh and 4 is april

i want to validate column month in a way that when i select "a" in type, in month dropdown only month 4 should be available

"s" should have both 3,4 in dropdown of month column

"w" should have only 3 in dropdown of month column

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D7y ago
Accepted answer
Oh sorry. In Excel, you need to create a list validation in A2 by click the data validation in menu bar and select source data range:

After that, set formula in B2:
Subin Thomas7y ago
hey leon,
can you tell me how to do this in excel sheet rather than coding
Leon D7y ago
You can use Free Spire.XLS library to achieve this.

1. Install Free Spire.XLS from nuget.
https://www.nuget.org/packages/FreeSpire.XLS/

2. Use code like below.
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\test.xlsx");

sheet.Range["A2"].DataValidation.DataRange = sheet.Range["C2:C3"];
sheet.Range["B2"].Formula = "VLOOKUP(A2,C:D,2,0)";

workbook.SaveToFile("Output.xlsx", FileFormat.Version2013);
Output.xlsx looks like:
When I select May in A2, B2 will show 5.

If my answer helps, please mark it as accepted, thank you :)