need help to find text in sheet1 and return column date but only if greater than today

[sumhungl0]

ok I have searched everywhere but cannot solve this problem I've been dealing with for over half a year. I have an excel sheet with a daily schedule of items. equipment id is the left column and dates across the third row. I need to find text in the sheet and get the date it falls under, but only if it is in the future. I have several years of dates and one column for each day going to the right. in sheet2 I have a summary of items coming due and what date they are under, in which I manually type in the due dates from sheet1. basically I scroll until I find the item then I go to sheet2 and type the date that it fell under. when items are scheduled on sheet1 by other users, I don't always know about it and then I have to go in frequently and look for new items. what I would like to do is just have the summary sheet2 read from sheet1 so that I can just see the upcoming items due at a glance.

I have tried to do a named dynamic range in which the name represents a date range of today and all dates in the future. unsuccessful. I cant ever get the today part to work. here is what I tried. I set cell A1 to =today().

named range

=INDEX(SHEET1!$3:$3,MATCH(SHEET1!A1,SHEET1!$3:$3,0)):INDEX(SHEET1!$3:$3,COUNTA(SHEET1!$3:$3))

I tried to do a few index match functions but they get hung up on dates from the past and future where the same items are found.

=IF(INDEX(SHEET1!3:3,MATCH("*"&"oil change"&"*",SHEET1!5:5,0))>=TODAY(),INDEX(SHEET1!3:3,MATCH("*"&"oil change"&"*",SHEET1!5:5,0)),"not scheduled")

and

=IFERROR(INDEX(SHEET1!3:3,MATCH("*"&"oil change"&"*",INDEX((LEFT(SHEET1!5:5,10)="*"&"oil change"&"*")*(SHEET1!3:3>=TODAY()),0),0)),"not scheduled")

those last two codes are the closest I have to working correctly. only problem is that if multiple "oil change" results are present in row 5 then I get wrong dates. in the past or future. can anyone help me? thank you.

 

[iliace]

I'm not sure I fully understand the issue (would be nice to see an example of the data layout), but this might be a solution.

This formula will display the date of the next scheduled "oil change" that is after the date in A1.

	A	B	C	D	E	F	G	H	I	J
1	1/4/2014
2
3	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan
4
5			oil change		oil change				oil change
6
7	1/5/2014

<tbody>
</tbody>

Worksheet formulas

Cell Formula A7 {=IF(INDEX(Sheet1!3:3,MATCH("*"&"oil change"&"*",IF(Sheet1!3:3>A1,Sheet1!5:5, 0),0))>=A1,INDEX(Sheet1!3:3,MATCH("*"&"oil change"&"*",IF(Sheet1!3:3>A1,Sheet1!5:5, 0),0)),"not scheduled")} <tbody> </tbody>

Array Formula: The brackets { } are inserted automatically, do not type them in. Confirm the formula using CTRL+SHIFT+ENTER

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[sumhungl0]

perfect, thank you so much. i couldnt get the not scheduled part to work so i just wrapped the whole thing in an iferror. but other then that it seems to work for every scenario. thanks again.

 

[iliace]

Glad to hear, thanks for the feedback!

 

[Peter_SSs]

Here's a bit shorter, non-array, option that may also do what you want.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2
3	01-Jan-14	02-Jan-14	03-Jan-14	04-Jan-14	05-Jan-14	06-Jan-14	07-Jan-14	08-Jan-14	09-Jan-14	10-Jan-14
4
5			a oil change	grease	oil change b				c oil change
Sheet1

Excel Workbook
	A
1	03-Jan-14
2
3	05-Jan-14
Sheet2