Dynamic Charting By Dates

  • Thread starter Thread starter StonyfieldRob
  • Start date Start date
S

StonyfieldRob

This is in reference to Jon Peltier's article on dyamic charts.
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

My question is the example given is using data that goes down a column.

Column A is the dates, Column B is the data, ect...

My data flows across rows, so all my dates are in row 1 and the data is in
row2.

I would like some help converting Mr. Peltiers technique to fit my need.

This code needs to go across a row instead of down a column.

=OFFSET(AllDates,MATCH(StartDate,AllDates,1)-1,0,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1,1)

As well as this one I presume.

=OFFSET(ChartDates,0,1)
 
From Excel's help, the syntax for the OFFSET function is

OFFSET ( reference, rows, columns, height, width )

Each input is separated by commas.

You'll need to do two things:

(1) swap the position of the row and column reference.
(2) swap the position of the height and width reference.

As an example, the present formula:

=OFFSET( AllDates, MATCH(StartDate,AllDates,1)-1, 0
,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1 ,1 )

would then become:

=OFFSET( AllDates, 0, MATCH(StartDate,AllDates,1)-1, 1,
MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1 )

The formulas are complex so you may need to play with them a bit. I've not
tested the results but conceptually the repositioning of the inputs should
work.

For the second formula, the syntax would be:

OFFSET ( reference, rows, columns )

You would need to swap the position of the row and column reference.
 
Back
Top