Dynamic Chart Question

[webharvest]

I have a twist on the "Excel Dynamic Chart #10: OFFSET Function Dynamic Range" method.

I want to create a chart that only shows the sum of hours a person works for the four weeks after the current Sunday. So I have all the Sundays (Dates) in 2011 listed from J7 to BI7. From row 8 to row 40 I list different jobs and how many hows different people are working. I set up a filter based on the person's name because I would like to see if someone is "over booked" for the week. To know how many hours are scheduled for a week I put the following formula in J6 to BI6 SUBTOTAL(9,BG8:BG40). This way it will only sum the visible filtered items.

Now the hard part that I can't figure out. In cell B3 I put =TODAY()+1-MOD(WEEKDAY(TODAY()),1) to return the current Sunday to come. In B4 I put the number of weeks I want to look beyond the current Sunday. So for me I put the number 4.

Now I tried to use the tricks I learned from "Excel Dynamic Chart #10" tutorial but switched to columns instead of rows. First I needed to find the cell that contains the current Sunday to come. I did this using the following formula =CELL("address",INDEX($A$7:$BI$7,,MATCH($B$3,$A$7:$BI$7,0)))

I was hoping I could put the above formula into the offset formula to have it start at the "Current Sunday to come" location and set the chart to show 4 (B4) Sunday's Data (Row 6) following it
=OFFSET((CELL("address",INDEX($A$7:$BI$7,,MATCH($B$3,$A$7:$BI$7,0)))),,((CELL("address",INDEX($A$7:$BI$7,,MATCH($B$3,$A$7:$BI$7,0)))):$BI$7)-$B$4,,$B$4)

I used the CELL formula by itself and it returns the right column $AC$7 but when I evaluate the OFFSET formula the column value comes back as invalid. Since the formula is not valid I can't use it to define a new name with it and then use it in the chart.

I'm sorry for this being long and drawn out but I think this type of application of the dynamic chart would be useful to others and I've spent a bunch of time trying to figure this out on my own and I'm stumped.

Thanks ahead of time for the help!

 

[AlphaFrog]

I'm not sure I follow everything, but I think you may be over engineering it though.

This dynamic range formula will return a range of cells within J6:BI6 where

• Start cell = Match the date in J7:BI7 that has the next Sunday from today
• Range width = the number of weeks from cell B4

=OFFSET(J6,0,MATCH(TODAY()-1,J7:BI7,1),1,B4)

The MATCH function MATCH(TODAY()-1,J7:BI7,1) finds the cell offset from J7 that has the next Sunday's date from today. If today is a Sunday, it matches today's date.


Cell B4 is the number of weeks and it is referenced in the OFFSET formula as the range width argument.

 

[webharvest]

Thanks that is perfect solution and does exactly what i wanted. I was definitely over thinking it.