HOME > SOFTWARE > EXCEL
Choice of a list with a condition in Excel
There are two sheets, in which there is two colums :
– from A1 to A3 with dates (example : 01/01/2016),
– from B1 to B3 with titles (example : formation n°1),
In a second sheet, there are :
– in A1, the list of the titles (with a scrolling menu, related to the sheet 2 B1:B3),
– in A2, a cell where we indicate the free date,
How to put in the A3 cell the formula that allow to say : if the date entered in the cell in more ancient than a date related to this title in sheet 2 then FALSE, otherwise TRUE.
Example :
Sheet 2
A B
01/04/2016 Formation 1
01/05/2016 Formation 2
01/06/2016 Formation 3
Sheet 1
A1
Formation 2
A2
01/04/2016
A2
FALSE (because the date for this formation is more ancient than the referential > Formation 2 that starts from 01/05/2016
?
The formula is the following (in A3) :
=SI(A2="";"";SI(A2 < INDEX(Feuil1!A:A;EQUIV(A1;Feuil1!B:B;0));"False";"True"))
– from A1 to A3 with dates (example : 01/01/2016),
– from B1 to B3 with titles (example : formation n°1),
In a second sheet, there are :
– in A1, the list of the titles (with a scrolling menu, related to the sheet 2 B1:B3),
– in A2, a cell where we indicate the free date,
How to put in the A3 cell the formula that allow to say : if the date entered in the cell in more ancient than a date related to this title in sheet 2 then FALSE, otherwise TRUE.
Example :
Sheet 2
A B
01/04/2016 Formation 1
01/05/2016 Formation 2
01/06/2016 Formation 3
Sheet 1
A1
Formation 2
A2
01/04/2016
A2
FALSE (because the date for this formation is more ancient than the referential > Formation 2 that starts from 01/05/2016
?
The formula is the following (in A3) :
=SI(A2="";"";SI(A2 < INDEX(Feuil1!A:A;EQUIV(A1;Feuil1!B:B;0));"False";"True"))
Note: Commissions may be earned from the links above.
This page contains references to products from one or more of our advertisers. We may receive compensation when you click on links to those products. For an explanation of our advertising policy, please visit this page.
Related posts:
Excel macro to insert a picture with Redime
https://w3ask.com/excel-macro-insert-picture-redime/
https://w3ask.com/excel-macro-insert-picture-redime/
You want to save a macro that will allow you to insert a picture (located in a directory of your hard drive disk) in an active cell where you ...
How to add lines with a VBA code in Excel
https://w3ask.com/how-add-lines-vba-code-excel/
https://w3ask.com/how-add-lines-vba-code-excel/
If you want to automatically add line in your Excel sheet, you can use a VBA code. The following example will add lines after the N column in ...
How to convert a number in time with Excel ?
https://w3ask.com/convert-number-in-time-excel/
https://w3ask.com/convert-number-in-time-excel/
When we enter a date with points, is there a possibility to convert it automatically into a date with slashes, so that it could be taken into ...
How to get the maximum value in Excel
https://w3ask.com/maximum-value-in-excel/
https://w3ask.com/maximum-value-in-excel/
We have two column, A and B, with towns in A and numbers in B. We would like to determine the maximum value in B, the city with the biggest ...
Excel VBA, test of the And (&)
https://w3ask.com/excel-vba-test-of-the-and/
https://w3ask.com/excel-vba-test-of-the-and/
In an Excel VBA, how to detect a cell with a & character in a For loop ?
You don’t necessarily need a loop in that case, here is an example ...