Forum Discussion
Excel 2010 - Auto update chart
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.
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.
Thank you I understand it better now. I figured only needed to do one reference for the Dates.