The following formula is in cell A10. Right now the formula returns data if the date in B5 matches with a date in column L on the Enter Executions worksheet. I would like to change the formula to return data if the dates in BOTH B5 and B6 match with a date in column L on the Enter Executions worksheet. Can anyone help?
Please Login or Register to view this content.
Last edited by rhudgins; 10-26-2011 at 02:55 PM.
if teh dates are different in b5 & b6 try an if(or( statement before your equation.
if the dates are the exact same in b5 & b6 then try this. if you create another column next to your two date columns and concatenate the two dates together, i.e.- a1= 10/2/11 & b1= 10/2/11, then =concatenate(b5," ",b6) and excel will return two serial numbers i.e.- 40553 40553 in b7 lets say. then turn your date that you are referencing in Column L into a serial number by making the format a text. Then you can do an if(or(left(b7,5),right(b7,5)=L,your equation,"").
im sure theres other ways to do this but when using dates, the concatenation is a useful way to reference. If this is confusing type into google, "double vlookup" and you should find whay youre looking for.
The dates are different in B5 & B6. Below I tried an if (or( statement in cell A10. The second logical of the If(or( statement references cell B$6. I am recieving an error that the formula contains an error. Any ideas why?
={IF(OR(IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$5,IF('Enter Executions'!$L$2:$L$100000<$B$5+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))),ROWS($A$1:$A1))),""),IFERROR(INDEX('Enter Executions'!A$2:A$100000,SMALL(IF(('Enter Executions'!$H$2:$H$100000=$B$4)+('Enter Executions'!$H$2:$H$100000=$B$4&" 2nd Leg"),IF('Enter Executions'!$L$2:$L$100000>=$B$6,IF('Enter Executions'!$L$2:$L$100000<$B$6+1,ROW('Enter Executions'!$B$2:$B$100000)-ROW('Enter Executions'!$B$2)+1))),ROWS($A$1:$A1))),"")))}
Can anyone help me with this?
Try this:
it assumes the same logic, that you want to check L against >=B6 and <B6+1...Please Login or Register to view this content.
Note: this is really a processor intense formula... if you can reduce those ranges that would be best.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks for your help. I applied the formula that you provided me to cell A10 and it is not returning a value. Can you take a look at my workbook and see what may be the problem?
This part: IF('Enter Executions'!$L$2:$L$100000<$B$5+1 and then this part IF('Enter Executions'!$L$2:$L$100000>=$B$6 are contradictory. B5 is Oct 18 and so column L must be less than Oct 18 + 1 (or Oct 19).. and at the same time B6 is Oct 19 and you are telling it that column L must be greater than or equal to Oct 19)...
what exactly are the conditions you need?
For the date conditions I am looking to return all of the values in the range A$2:A$100000 on the Enter Executions worksheet who's date occurs on 10/18/11 or 10/19/11 (the values in cell B5 and B6). Occasionally there will be no date at all in cell B6(blank cell) and therefor the formula should just return values that occur on the date in cell B5
Last edited by rhudgins; 10-26-2011 at 02:27 PM.
Try
CSE confirmedPlease Login or Register to view this content.
Thank you this is what I am looking for
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks