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]

With your first range in A1:E5
AND...
G1: ThisStart
H1: =TODAY()-WEEKDAY(TODAY(),3)....Currently Monday, 25-Mar-2013

G2: NextStart
H2: =H1+7 ....Currently Monday, 01-Apr-2013

and your second range in L1:N5
This regular formula returns the process step that pertains to this week

M2: =INDEX($A$1:$E$1&"",MAX(MAX(INDEX((($B2:$E2-WEEKDAY($B2:$E2,3))=$H$1)*COLUMN($B2:$E2),0)),1))

Copy that formula down through M5

This regular formula returns the process step that pertains to nest week

M2: =INDEX($A$1:$E$1&"",MAX(MAX(INDEX((($B2:$E2-WEEKDAY($B2:$E2,3))=$H$2)*COLUMN($B2:$E2),0)),1))

Copy that formula down through N5.

Note: if there is no match, the formula returns the contents of cell A1...so leave that cell blank.

Is that something you can work with?

 

[JoeMo]

A non-array, nested IF solution. Copy the formula in H2 across and down.
H2 formula is:

=IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($C2,2),$C$1,IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($D2,2),$D$1,IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($E2,2),$E$1,"")))

Sheet4

*	A	B	C	D	E	F	G	H	I
*	Start	Check 1	Check 2	Check 3	*	*	This Wk	Next Wk
Program A	*	Program A	*	*
Program B	*	Program B	*	Check 2
Program C	*	Program C	*	*
Program D	*	Program D	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:64px;"><col style="width:71px;"><col style="width:55px;"><col style="width:60px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1/25/2013[/TD]
[TD="align: right"]2/25/2013[/TD]
[TD="align: right"]4/25/2013[/TD]
[TD="align: right"]6/25/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2/13/2013[/TD]
[TD="align: right"]3/13/2013[/TD]
[TD="align: right"]4/3/2013[/TD]
[TD="align: right"]6/13/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4/26/2013[/TD]
[TD="align: right"]7/26/2013[/TD]
[TD="align: right"]8/13/2013[/TD]
[TD="align: right"]1/14/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]7/8/2013[/TD]
[TD="align: right"]7/15/2013[/TD]
[TD="align: right"]7/22/2013[/TD]
[TD="align: right"]7/29/2013[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 =IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($C2,2),$C$1,IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($D2,2),$D$1,IF(WEEKNUM(TODAY(),2)+COLUMN(A1)-1=WEEKNUM($E2,2),$E$1,""))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ChrisM]

Here's a third option, and probably the ugliest formula I've ever written:

=IFERROR(OFFSET($A$5,,SUM(IF(($E6:$G6>=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())-WEEKDAY(NOW())+1))*($E6:$G6<=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())-WEEKDAY(NOW())+7))>0,($E6:$G6>=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())-WEEKDAY(NOW())+1))*($E6:$G6<=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())-WEEKDAY(NOW())+7))*COLUMN($E6:$G6),0))-1),"")

This is an array formula, and it will only work for the "this week" column. You have to modify it for the "Next week" column by changing "weekday + 1" and "weekday + 7" sections to "weekday + 8" and "weekday + 14".

data sources:
E6:G6 is your raw data row

A5 is in the same row as your headers (check 1, check 2, etc). The array will return the column # of the match, then use this to offset from A5, so you'll need to adjust a little bit to get the right column. The part to adjust is the -1 in this segment "COLUMN($E6:$G6),0))-1)"

 

[SamAnnElizabeth]

Ron: I'm adopting it to fit in my sheet and I'm getting a #Value error because in my actual sheet there are some dates missing. For example, the Start/Program A date is in the past, so it's not in the table or there is no Check 2 for Program C.

Do you know a tweak that can fix this? My actual table is 18x27 so anything I can copy en masse is best!

Joe: Because my actual table is 18x27, I'm not sure how well this will copy.

Chris: I'm confused what you mean by a data source and what you have entered in E6:G6

 

[ChrisM]

E6:G6 is a sample row of your data, with the dates for check 1,2,3

 

[Ron Coderre]

Ron: I'm adopting it to fit in my sheet and I'm getting a #Value error because in my actual sheet there are some dates missing. For example, the Start/Program A date is in the past, so it's not in the table or there is no Check 2 for Program C.

Do you know a tweak that can fix this? My actual table is 18x27 so anything I can copy en masse is best!

1) Missing dates are easily absorbed by the formulas I posted. It will, however, return #VALUE! if any of the date cells has text...like: "LATE"... and not a date.
Is that the issue?

2) If you need us to supply a different formula, please let us know the exact locations of your data so we can tailor the formulas to suit your needs.

 

[SamAnnElizabeth]

Ron,

The cells it's drawing from should be blank, they're the result of this formula: =IF(M4="","",M4+IF(WEEKDAY(M4)=6,0,(-1-WEEKDAY(M4)))) which will return a Friday date for the one entered in a previous table. Maybe I should mention all of my dates are on Fridays. The blanks I have are pretty numerous. I've only gotten the formula to return a result in the rows that have no blank cells. I double checked and my dates are all in the date format.

When I did 'evaluate formula,' it seemed that the issue was in the line where the data row is subtracted from the weekday value of the data row: $B2:$E2-WEEKDAY($B2:$E2,3). Where there is a blank, the formula returned a #VALUE error.

I adjusted the formula you gave me to fit my data area. I have programs listed in column AB, with AB1 left blank. They fill until row 28. I have checkpoints in row 1 from AC to AT. I put the 'week beginning' dates in row 29, CF:CK. I adjusted the formula to read the following:
=INDEX($AB$1:$AT$1&"",MAX(MAX(INDEX((($AC2:$AT2-WEEKDAY($AC2:$AT2,3))=CF$29)*COLUMN($AC2:$AT2)-27,0)),1))

I added the -27 to compensate for the columns I was using.

Thank you so much for your help!

 

[SamAnnElizabeth]

I think I solved it! I changed the formula from the area I was drawing from to return '0' for the if function, so =IF(M4="","0",M4+IF(WEEKDAY(M4)=6,0,(-1-WEEKDAY(M4)))) which solved the blank cell problem.

Thank you so much for your help! I'm very thankful!

 

[SamAnnElizabeth]

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