Forum Discussion
Solved
If Cell ISBLANK then look at other Cell
Hello Everyone, I have a starting date (Sheet1!F8), and 2 dates to compare to (A6, and A7), If A6 or A7 is later than Sheet!F8, I want a name to display in the field (imported from Sheet1!F6). Ho...
So, you have two lesson dates in A6 and A7 (or that could be more, in A8, A9, etc.?). If the latest non-blank lesson date is greater or equal to starting date you display the name, otherwise nothing. Correct?
If so the formula could be
=IF(MAX(A6:A7)>=Sheet1!F8,Sheet1!F6,"")
Aaah, I see. No, the starting date is 'Sheet!F8'. A6 and A7 are the lesson dates.
I had set the starting date in 'Sheet!F8' at 2018-06-13, the same as A7 (to test) but then the name does not appear in the cell. (It does appear on any date set in A7 earlier).
What I hope to achieve is that whenever a new student might be added to a class, his/her name will only appear on lessons after his/her starting date.
I'm so sorry for the confusion. :'(
I had set the starting date in 'Sheet!F8' at 2018-06-13, the same as A7 (to test) but then the name does not appear in the cell. (It does appear on any date set in A7 earlier).
What I hope to achieve is that whenever a new student might be added to a class, his/her name will only appear on lessons after his/her starting date.
I'm so sorry for the confusion. :'(
So, you have two lesson dates in A6 and A7 (or that could be more, in A8, A9, etc.?). If the latest non-blank lesson date is greater or equal to starting date you display the name, otherwise nothing. Correct?
If so the formula could be
=IF(MAX(A6:A7)>=Sheet1!F8,Sheet1!F6,"")
- Works like a charm!!!
Thank you so much! Hi Martin,
That could be
=IF(SUMPRODUCT((A6:A7>=Sheet1!F8)*(A6:A7<=Sheet1!F9)),Sheet1!F6,"")
- Again, my gratitude for your help and solution!
I was wondering. Could I add an end date (Sheet1!F9)?
So if either A6 and/or A7 are between the dates of (Sheet1!F8) and (Sheet1!F9) to give the result of (Sheet1!F6), and if not " ".
So far I tried it using your formula and adding to it: =IF(MAX((A6:A7)>=Sheet1!F8,(A6:A7)<=Sheet1!F9),Sheet1!F6,"") but then it says one of the values is of the wrong data type (I checked all the cells for date formatting).
I also tried it with the AND operator: =IF(AND(A6:A7>=Sheet1!D8,A6:A7<=Sheet1!D9),"Sheet1!D6"," ") but it seems it doesn't work with 2 dates (A6:A7)
And I tried it with =SUMIFS(Sheet1!D6,A6:A7,">="&Sheet1!D8,A6:A7,"<="&Sheet1!D9) ... but I didn't think that would work as it needs to add values.
I'm not quite understanding how Excel "thinks" but I am learning a lot, in part thanks to you! - Yes, perfect! =D
Thank you so much!
I will work on better communicating questions about Excel formulas.