If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic Charting By Dates
This is in reference to Jon Peltier's article on dyamic charts.
http://pubs.logicalexpressions.com/P...cle.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) |
#2
|
|||
|
|||
Dynamic Charting By Dates
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. -- John Mansfield http://www.cellmatrix.net "StonyfieldRob" wrote: This is in reference to Jon Peltier's article on dyamic charts. http://pubs.logicalexpressions.com/P...cle.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) |
Thread Tools | |
Display Modes | |
|
|