WEEKNUM to dynamic range name

I need to take a column of dates starting from Jan 1 of the current year to Dec 31 of the current year (cells from A2 to A366) and assign each week of that list by the function WEEKNUM and assign it to a dynamic range name.

I just don't have the Excel experience to know the best way or what the most efficient options are.

I know I can use the function OFFSET to help, but I'm not sure how to work the "reference" argument dynamically. For a static example Week1=OFFSET(A2,7,1) Week2=OFFEST(A9,6,1) Week3=OFFEST(A16,6,1)etc. works statically for the year 2006, but for the year 2007 Week1=OFFSET(A2,6,1) Week2=OFFSET(A8,7,1) Week3=OFFSET(A15,7,1) etc.

From year to year I need the WEEKNUM to dynamically reference the correct range of cells associated with it's WEEKNUM.

Ultimately I am using these dynamic range names to sum values in cells that contain daily sales figures in each department so I can reference week to week each year.

Thanks.

The Weeknum function won't accept a range argument

The easiest way to do this is to add another column to give the weeknum, e.g. in Z2 use

=WEEKNUM(A2) where A2 contains your date

You can then sum for a particular week, e.g. week 23 by using a formula like

=SUMIF(Z2:Z1000,23,M2:M1000)

where column M contains your sales figures

try this idea changing the +6 to +13 +20, etc
=OFFSET(Sheet12!$A$1,MATCH(DATE(YEAR(TODAY()),1,1)+6,Sheet12!$A:$A),0,7,1)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"additude" <additude.2bed24_1153656006.8826@excelforum-nospam.com> wrote in
message news:additude.2bed24_1153656006.8826@excelforum-nospam.com...
>
> I need to take a column of dates starting from Jan 1 of the current year
> to Dec 31 of the current year (cells from A2 to A366) and assign each
> week of that list by the function WEEKNUM and assign it to a dynamic
> range name.
>
> I just don't have the Excel experience to know the best way or what the
> most efficient options are.
>
> I know I can use the function OFFSET to help, but I'm not sure how to
> work the "reference" argument dynamically. For a static example
> Week1=OFFSET(A2,7,1) Week2=OFFEST(A9,6,1) Week3=OFFEST(A16,6,1)etc.
> works statically for the year 2006, but for the year 2007
> Week1=OFFSET(A2,6,1) Week2=OFFSET(A8,7,1) Week3=OFFSET(A15,7,1) etc.
>
> From year to year I need the WEEKNUM to dynamically reference the
> correct range of cells associated with it's WEEKNUM.
>
> Ultimately I am using these dynamic range names to sum values in cells
> that contain daily sales figures in each department so I can reference
> week to week each year.
>
> Thanks.
>
>
> --
> additude
> ------------------------------------------------------------------------
> additude's Profile:
> http://www.excelforum.com/member.php...o&userid=36661
> View this thread: http://www.excelforum.com/showthread...hreadid=564085
>

Well,

I believe that was the answer. I had already created the column of WEEKNUM and basically applied your suggestion of SUMIF and it looks like it is working.

Thanks!

Can you think of a way to do it without creating the column of WEEKNUM's? and maybe incorporating it into the SUMIF?

Thanks for your help.

=SUMPRODUCT(--(1+INT(($A$2:$A$366-(DATE(YEAR($A$2:$A$366),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$366),1,1))))/7)=week_to_test),B$2:B$366)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"additude" <additude.2beh84_1153661407.7212@excelforum-nospam.com> wrote in
message news:additude.2beh84_1153661407.7212@excelforum-nospam.com...
>
> Well,
>
> I believe that was the answer. I had already created the column of
> WEEKNUM and basically applied your suggestion of SUMIF and it looks
> like it is working.
>
> Thanks!
>
> Can you think of a way to do it without creating the column of
> WEEKNUM's? and maybe incorporating it into the SUMIF?
>
> Thanks for your help.
>
>
> --
> additude
> ------------------------------------------------------------------------
> additude's Profile:
http://www.excelforum.com/member.php...o&userid=36661
> View this thread: http://www.excelforum.com/showthread...hreadid=564085
>

Originally Posted by Bob Phillips

=SUMPRODUCT(--(1+INT(($A$2:$A$366-(DATE(YEAR($A$2:$A$366),1,2)-WEEKDAY(DATE(YEAR($A$2:$A$366),1,1))))/7)=week_to_test),B$2:B$366)

That's why I said that a column with WEEKNUM was the easiest way...
....although I think you can simplify Bob's suggestion very slightly to

=SUMPRODUCT(--(INT(($A$2:$A$366-DATE(YEAR($A$2:$A$366),1,1)-WEEKDAY($A$2:$A$366))/7)+2=week_to_test),B$2:B$366)