Re: how do i update a chart area automatically

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You use the same idea with the OFFSET formula. The syntax of OFFSET is:

OFFSET(range, offset rows, offset columns, resize rows, resize columns)

so your month label range would have a "refers to" definition like

=OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$137))

This assumes only the dates appear in row 137, and that a date header is not
added until data for the date is added. The easiest way to define the value
ranges with respect to the month range (MonthRange below) is:

=OFFSET(MonthRange,1,0)
=OFFSET(MonthRange,2,0)
=OFFSET(MonthRange,3,0)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Kay" <Kay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:486A052D-83FB-4B9C-9AD3-9A8EDD9F031F@xxxxxxxxxxxxxxxx
> Hello,
>
> I have an offset formula I use to update a chart plotted by columns. I
> name
> the ranges and change the range areas to the offset formula...something
> like
> this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes
> all
> the rows available for charting minus the row with the label. I use this
> same technique for two columns...Column A acts as the Category x axis and
> Column B as the value axis. Works beautifully
>
> Now, I would like to come up with a way to plot by rows. My values would
> grow as new field names would be added across the columns...specifically,
> Jan, Feb, March and then have April, May and June added to the plot area
> automatically.
>
> C138:C147 would have the names of sales reps and D137:F137 would hold the
> Month labels and then D138: F147 would list the values. So the list of
> sales
> reps is static, but new data comes in for each month and needs to be added
> to
> the charts. I have many of these charts on a dash board and do not want
> to
> update all of them every month. Any suggestions would be appreciated
> alathough I want to use VBA as a last resort.
>
> Thanks!


.

Relevant Pages

  • Re: referring to segment offsets in read address mode
    ... I'm still not sure why one label ... declared in masm's uninitialized data gets translated by the assembler as ... section gets translated as an offset from _bss. ...
    (comp.lang.asm.x86)
  • Re: how do i update a chart area automatically
    ... OFFSET(range, offset rows, offset columns, resize rows, resize columns) ... Jon Peltier, Microsoft Excel MVP ... the rows available for charting minus the row with the label. ... I have many of these charts on a dash board and do not want ...
    (microsoft.public.excel.charting)
  • Re: Removal of GEOM_BSD, GEOM_MBR, GEOM_PC98 and GEOM_SUNLABEL
    ... GEOM_LABEL: Label ufsid/46387cd616292ca8 removed. ... Mediasize: 9568387584 ... Sectorsize: 512 ... offset: 32256 ...
    (freebsd-current)
  • Re: How do I place a merge data field in a specific location on a labe
    ... "Microsoft Word ignores the \y switch if you specify a location outside the ... spreadsheet on the bottom line of the label, ... ADVANCE \y xx} option, where xx is the offset to the last line in points, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Platte klonen
    ... den BSD-Label im Ziel mit überschreiben. ... Die Partitionen leg ich immer brav mit sysinstall an, da sollte der Offset ja dann 63sein. ... falls der Label von ad1s1 anders ist als der von ad0s1), ...
    (de.comp.os.unix.bsd)