Array to copy/paste as column headings?

[thart21]

I have an Access table linked to an Excel workbook (Sheet2)with these fields:

Months (5/1/2011, 6/1/2011,etc)
DataDate (5/1/2011,5/2/2011,5/3/2011,5/4/2011, etc)
WeekNo (WEEK1,WEEK2,WEEK3,WEEK4)

On Sheet1 of my workbook, I have 4 sections (1 for each WEEK#) and a dropdown for Month. Across the top of each section I have 5 columns, 1 for each DataDate with data that is pulled below that from my Access table. All is working great for that.

The one thing I can't seem to get figure out is how, when the Month is selected from the dropdown, to get the DataDate to change across the column headers for each Weekly section. A co-worker suggested using arrays so I am trying to find a way to do this.
Is this something that could be done using an array? Is it the best method? I just need to copy/paste the existing DataDates for each WEEK# and Month as column headers.

When I use the MrExcelHtml, the dropdown does not appear, but it is around cell D5. The example is 1 of 4 sections that will be exactly the same but with different Week#'s.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
4
5
6
7
8		Equipment
9		June	WEEK 1
10		Date	6/1/2011		6/2/2011		6/3/2011		6/6/2011		6/7/2011		FORECAST
11		Total Demand	1,679,942		1,034,553		1,699,942		1,681,521		#N/A
By Day

Excel 2007
Would appreciate any help anyone could give me with this one, thanks!

Toni

 

[Rijnsent]

Hi Toni,
nice challenge . I think I have it working with this formula in a testsheet I made, it's indeed an array formula (create the formula and then type CTRL+SHIFT+ENTER, see also the Excel help files):
={MIN(IF($B2:$B21=$G$3;IF($C2:$C21=$H$3;IF($D2:$D21>G6;$D2:$D21;100000);100000);100000))}

So what does this do?:
-I'm assuming you have a list somewhere with your dates, months and weeks, something like (my example lists starts at B2 and runs to D21):
Mnth - Week - Date
june - WEEK 1 - 1-jun-11
june - WEEK 1 - 2-jun-11
june - WEEK 1 - 4-jun-11

-Next, what you do want to do is to find the first date for that week and that month: the minimum that is. So my "$B2:$B21=$G$3" is to match the month, the "$C2:$C21=$H$3" is to match the weekname.
-The $D2:$D21>G6 implies that the actual date has to be compared with a possible date found before (so for the second day in the week G6 would point to the first date, for the first date it can point to an empty cell).
-Why the 100000? Because I'm looking for a date (nowadays round 41000 numerical) and the minimum, so I need a big number for all the "FALSE" cases (month not matching etc.), if I leave it empty or put 0, that will always be the minimum...

Hope this gets you moving,
Koen