dynamic named range starting from new position each week

[ajm]

hi to all,

I am struggling with a dynamic named range formula that will capture 15 weeks of data from the current week back. so, my layout is data laid out by branch (each branch on new row) and by week (week 1 is C1, week 2 is d1, etc). currently reporting the 15 weeks back from Week 31, so my data set will start in column S.

I have tried to specify the width as 15 in a named range cell called HSize, using this formula:

OFFSET('Graph Data'!$C$1,0,0,1,HSize)

. this anchors the start point of the range to C1.

so i thought, righto, will just make the reference point dynamic as well. no problems!??????

HOw do i specify the start cell when it changes each week?

 

[Marcelo Branco]

Hi ajm,

I'm not sure if i understood correctly, but maybe this
=OFFSET('Graph Data'!$C$1:$Q$1,ROW()-1,COLUMN()-17)

HTH

M.

 

[ajm]

solved:

=OFFSET('Graph Data'!$C$1,0,'Graph Data'!$A$1-17,1,15)

this sets my named range to start 16 columns from C1 and will be 1 row high and 15 cols wide.

extra detail: Col A2:A6 contains Branch names and Col B (B2:B6) Branch codes. Data for each branch is in C2:AG6.
- Row 1 Contains the count of columns used (A1, = 33) and the week numbers starting C1:AG31.

code then says: OFFSET(C1,0,16,1,15), and next week it will be OFFSET(C1,0,17,1,15), etc etc

 

[ajm]

Hi ajm,

I'm not sure if i understood correctly, but maybe this
=OFFSET('Graph Data'!$C$1:$Q$1,ROW()-1,COLUMN()-17)

HTH

M.

thanks marco, i could not get your formula to work. i think because, using ROW() and Column() means that the offset is trying to move to one row up and 17 back from wherever your active cell is. thanks all the same. If you see above, i found a work around that does the trick.

cheers,

ajm

 

[jasonb75]

This may or may not be of any use, assuming always the last x number of weeks try,

=OFFSET('Graph Data'!$A$1,,'Graph Data'!$A$1,,-HSize)

Using that, if, for example you changed HSize to 10, it would give you the last 10, your current version would give the first 10 of the last 15 unless you edit the formula first.

 

[Marcelo Branco]

ajm,

Using Row with a positive value i'm trying to move 1 (or more) row(s) down not up.

Anyway, my previous formula needs an improvement to work with 1st 15-weeks

=OFFSET('Graph Data'!$C$1:$Q$1,ROW()-1,IF(COLUMN()>17,COLUMN()-17,0))

How do you get it to work?

M.