Array Formula?

[SamAnnElizabeth]

I think I'm close to finally figuring out my issue and I think I need to use an array formula. The trouble is, I'm not too great at array formulas. Here's a simplified version of what I'm working on.

I have a sheet that has a list of programs down column A. Across row 1 I have the check dates when something about its progress needs to be filed. Here's a fictional example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Start[/TD]
[TD]Check 1[/TD]
[TD]Check 2[/TD]
[TD]Check 3[/TD]
[/TR]
[TR]
[TD]Program A[/TD]
[TD]1/25/13[/TD]
[TD]2/25/13[/TD]
[TD]4/25/13[/TD]
[TD]6/25/13[/TD]
[/TR]
[TR]
[TD]Program B[/TD]
[TD]2/13/13[/TD]
[TD]3/13/13[/TD]
[TD]4/3/13[/TD]
[TD]6/13/13[/TD]
[/TR]
[TR]
[TD]Program C[/TD]
[TD]4/26/13[/TD]
[TD]7/26/13[/TD]
[TD]8/13/13[/TD]
[TD]1/14/14[/TD]
[/TR]
[TR]
[TD]Program D[/TD]
[TD]7/8/13[/TD]
[TD]7/15/13[/TD]
[TD]7/22/13[/TD]
[TD]7/29/13[/TD]
[/TR]
</tbody>[/TABLE]

There is no consistent progression. What I'm trying to do is find a way to create a list of the upcoming checks with a 'This Week', and 'Next Week' out look. I'd like it to look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]This Week[/TD]
[TD]Next Week[/TD]
[/TR]
[TR]
[TD]Program A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program B[/TD]
[TD][/TD]
[TD]Check 2[/TD]
[/TR]
[TR]
[TD]Program C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there an automated way I can get 'Check 2' to appear in that box by using an array? I've tried to figure it out, but I'm lousy at them. I've asked this question before but I think I have a slightly better grasp on it now.

 

[Ron Coderre]

I see you've discovered that empty text...""...is NOT blank!

 

[Ron Coderre]

Is there a way to get it to display two results if there are two matches in the row?

Excel does a poor job of concatenating results using a single formula. You'd need to either repeat the formula...returning the 1st, 2nd, etc largest matches (cumbersome)...or create a user defined function (UDF) with VBA.
Another alternative might be to use "helper" cells. They contain pieces of the puzzle and are used by other formulas to create the results you want. It's usually the same amount of formulas, they're just spread over multiple cells and can be easier to understand.

 

[SamAnnElizabeth]

I think I figured out listing two results if they exist as well, I'm using this:

=INDEX($AB$1:$AT$1&"",MAX(MAX(INDEX((($AC10:$AT10-WEEKDAY($AC10:$AT10,3))=CF$29)*COLUMN($AC10:$AT10)-27,0)),1))&" "&IFERROR(INDEX($AB$1:$AT$1&"",MAX(LARGE(INDEX((($AC10:$AT10-WEEKDAY($AC10:$AT10,3))=CF$29)*COLUMN($AC10:$AT10)-27,0),2),1)),"")

I repeated the formula, but changed the second MAX function to LARGE and returned the second largest number. It seems to be working so far. I'm trying to get the results to show up in a different sheet, but the INDIRECT formula is not working quite right. Is there a better way?

 

[Ron Coderre]

You shouldn't need the INDIRECT function to get the results you want.
Just prepend a proper sheet reference to the impacted ranges.
Example:
If the data ranges are on a sheet named My Data, the first formula would look like this:

 =INDEX('My Data'!$AB$1:$AT$1&"",MAX(MAX(INDEX((('My Data'!$AC10:$AT10-WEEKDAY('My Data'!$AC10:$AT10,3))=CF$29)
*COLUMN('My Data'!$AC10:$AT10)-27,0)),1))
&" "&
IFERROR(INDEX('My Data'!$AB$1:$AT$1&"",MAX(LARGE(INDEX((('My Data'!$AC10:$AT10-WEEKDAY('My Data'!$AC10:$AT10,3))=CF$29)
*COLUMN('My Data'!$AC10:$AT10)-27,0),2),1)),"")

Does that help?

 

[SamAnnElizabeth]

It works until I have to sort. I realized this problem when i first started working in the 'My Data' table and was able to use indirect cell references for the program names, which over came the problem. When I use the formulas you wrote in the My Data sheet they adjust based on the sort, but those that I have in the first sheet ('Program') do not adjust. I think using an Indirect cell reference again would solve this, but it's not working for me.

Any ideas?

 

[Ron Coderre]

Are you saying that when you sort the data range on the My Data sheet, the program labels get shuffled around so our formula no longer references the correct program rows?
If yes...then we're rapidly approaching the threshold where single-cell formulas will no longer be practical or efficient. We may need to go with helper columns. The alternative is to expand the functionality of the formula so it is able to find the correct row(s) and calculate on them. My preference is to always explore other options before resorting to INDIRECT. That function also has its issues. The largest being that it's volatile and can make a workbook needlessly sluggish. Since we're well into the "Oh, one more thing" cycle, we may also need you to make a sample workbook available so we can see the issues you're facing.