Excel 2010 - Auto update chart

[NadineC51]

I'm trying to figure out how to rewrite the offset for data that goes in different direct.  i.e.

https://support.microsoft.com/en-us/help/183446/how-to-use-defined-names-to-automatically-update-a-c...

I have my month/years going across the top in A1 to BI1  (Jan 17 to Dec 2021)

And various data titles going down in column A1 with each months values going across

I know I have to define the names for each section, but trying to figure out the =offset formula for the Refs to. 

I was able to get it to work in the example provided in the above link, but now trying to understand the offset formula to get it to work in different direction.  I guess if I understood the formula that would help, never used the =offset before.

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

=OFFSET($B$2,0,0,COUNTA($B:$B)-1)

Not too sure how to rewrite the above for different direction going across instead of down.

Example

                                                   Jan-17    Feb-17    Mar-17    Apr-17
Volume Estimates/Actuals    
Desk A MTD YoY Contacts       17,033    17,026      17,160    16,484
Desk A Actual Contacts             18,732    15,956      21,191    15,890
Desk A in one chart

Desk B MTD YoY Contacts        59,132     55,649     58,599    60,248
Desk B MTD Actual Contacts     57,257     49,270     56,795    49,403
Desk B in another chart

Column A continues down with different subjects with monthly values, each section will need its own chart.

As we add months and there values want the charts to auto update and add those months only. 

 Thanks

[#2 · Accepted solution]

Hi Nadine,

1) OFFSET simply gives you another way to define the range, e.g.

OFFSET(A1,0,0,3) is equivalent to A1:A3
OFFSET(A1,0,0,1,3) is equivalent to A1:C1

two optional parameters at the end are height and width of the region.

2) Since all data for your charts are in one column perhaps you shall calculate not entire number of records in column A, but number of records for each chart (e.g. count number of records which starts from "Desk A", but it very depends on how your data looks like). Plus you shall define where records for each chart starts and use it in OFFSET. Will be like

OFFSET(A1, <first row for the chart>-1,0,<number of rows for the chart>)

where above numbers are calculated by formulas

3) With horizontal axis (dates) is easier, it's only one row. You may calculate OFFSET as above, second variant. Or if your columns to the right are empty to take the range for the graph with gap, e.g A2:ZZ2 for the axis and similar for data. Empty cells will be ignored and won't affect your chart, no need in dynamic range for such case.

[#3]

Thank you I understand it better now.  I figured only needed to do one reference for the Dates.