Help with OFFSET Function

[BenCrockett]

I am re-building SEVERAL Reports and automating them - now that I have SQL access; I have a query that dumps the data I need. I have a weekly stats report, which tracks 56 weeks of performance data, for our Tech Support centers.

I have the Charts Dynamic somewhat – but what I would like to do, is have the OFFSET function dynamic as well, so it only looks at the current week and the past -56 weeks, I have tried COUNT, COUNTA, COUNT IF.. and I cannot get this figured out. I just want to the dump the data into the table, and be done with it. Haha.. I don’t have time to update the ranges.
I have looked through the Forum at similar topics on creating a dynamic ranges in the =OFFSET function.. And I don’t know what I am missing. And I am stuck, please help!
Not sure if this matters, but I use a FORM Control so the users can select which Metric on the chart they want to see - on the Dashboard Tabs..

My Offset functions you can see in the Name Manger - you can see what I am doing there, hopefully.

Anyway, I've attached my report – any help would be greatly appreciated.

----ADDING so.. I don't think in my case, I need to make my offset function dynamic - here's why.. My Offset Function is based off a preset range. If you look in the file, in Name Manger, the ranges the Offset function is looking at refers to LABELS =OFFSET(LABELS,Data!$b$73,0) I've moved the ranges in LABELS - and it works.. so if I can get the range in the LABELS to count or something...this would solve my issue (I hope I am making sense)

[BenCrockett]

Sorry, will add, I am doing an Offset of a Range in the Name Manager - the "LABELS1 > 6" are my ranges, I am doing the offset function.. so maybe I just need to make those ranges Dynamic, because the OFFSET function, is based on that "pre-set" ranges.. how would I accomplish that.. I'm lost for solutions..

[FDibbins]

Your dates are actually text, that just looks like a date. Perhaps if you converted then to actual dates, it would make it easier to do the ranges?

An alternative might be to add a helper row, sequesntially numbered (1,2,3 etc) and then base the range on that? =max(range):Max(range)-56...something lik ethat

[BenCrockett]

Okay, done.. But my real issue is, how do I get the "LABELS" ranges to be dynamic?

[BenCrockett]

Your dates are actually text, that just looks like a date. Perhaps if you converted then to actual dates, it would make it easier to do the ranges?

An alternative might be to add a helper row, sequentially numbered (1,2,3 etc) and then base the range on that? =max(range):Max(range)-56...something lik ethat

Let me see if that works..

[BenCrockett]

Let me see if that works..

No.. this doesn't work, either I am doing the formula wrong, or something, but I get an invalid reference..=HATETHISSOMETIMES

[FDibbins]

which 1 did you try?

[BenCrockett]

which 1 did you try?

=MAX(range):Max(range)-56 and using (1,2,3) <---Not sure how to base a range off that.. so I could be doing it wrong.

[FDibbins]

This doesnt directly answer your question, but you sould be able to modify this for your ranges.

I skipped both of my suggestions, and used this instead (and yes, I know uyou didnt want a sum(), but it shows how to use the offset() function)...
=SUM(OFFSET($A$1,1,COUNTA(1:1)-55,,56))

[BenCrockett]

Ha! Works! THANK YOU!! You da'man.

[FDibbins]

haha Happy to help