Dynamic Range across columns 1

[mpm32]

Hi, I am trying to create a dynamic range to use for sparkline charts that I have in another workbook.

My data for each chart is in a row and the new data will be added to the last column each month.

Data arranged like this

    G    H I J K L M N O P Q
2 Data   2 3 4 5 7 2 1
3 Data2  2 3 4 5 7 2 1

All of the examples of naming a dynamic range using offset have been expanding the range down rows, not across columns.

Along with many others I have tried - =OFFSET(RawData!$H$2,0,0,COUNT(RawData!H2A2)-1)

Doesn't seem to work.

For my sparkline, I would like to graph the last 6 months.

So in my example, I would like the series to include row 2 columns I thru N.

How would I write this for the named range?

In advance, thanks for your help.

 

[SkipVought]

hi,
[tt]
=OFFSET(RawData!$H$2,0,0,1,COUNT(RawData!H2A2)-1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Thanks it's always the little things that I miss.

If I wanted to have the range as a rolling six month, how would I do that?


Thanks again for your help! I am just learning about named ranges and the fact that they can be dynamic will speed up my monthly reporting.

 

[SkipVought]

[tt]
=OFFSET(RawData!$H$2,0,RollingPointer-1,1,6)
[/tt]
where RollingPointer would be the number of months you want to SKIP before starting the 6 month range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Hmm, I tried that and it will give the block of 6 months that I specify. But when I add the data for the next month, the range doesn't move.

It seems the range is static in the example you provided.

I think that's what I was trying to do with the count function in my first example.

I would like the formula to count the number of columns with data and select the most current 6 months.

 

[SkipVought]

[tt]
=INDEX(OFFSET($H$2,0,COUNTA(2:2)-7,1,6),1,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

That's the one, thanks a lot!