Data ange for horizontal dynamic charts

Hi all

Am creating varying graphs for reports and have vertical dynamic charts working well...

I have also been playing with Horizontal dynamic charts and have these working well if I copy and paste the data in reverse order - inserting a column to add 'new' data to the first column doesn't appear to work....

I obviously have the ranges incorrectly set as i would like the chart to read the data from the last column - I don't want to have to 'fiddle' with formatting the data...

I would simply like to add the most recent to the end (next blank column)...

Please see attached dummy file for your reference

any assistance greatly appreciated

S :-)

Originally Posted by shaz0503

Hi all

Am creating varying graphs for reports and have vertical dynamic charts working well...

I have also been playing with Horizontal dynamic charts and have these working well if I copy and paste the data in reverse order - inserting a column to add 'new' data to the first column doesn't appear to work....

I obviously have the ranges incorrectly set as i would like the chart to read the data from the last column - I don't want to have to 'fiddle' with formatting the data...

I would simply like to add the most recent to the end (next blank column)...

Please see attached dummy file for your reference

any assistance greatly appreciated

S :-)

ActiveMembers =OFFSET(Sheet1!$J$21,0,0,1,MATCH(1E+306,Sheet1!$I$21:$IV$21 ))
ExitedMembers =OFFSET(Sheet1!$J$24,0,0,1,MATCH(1E+306,Sheet1!$I$24:$IV$24))
InactiveMembers =OFFSET(Sheet1!$J$22,0,0,1,MATCH(1E+306,Sheet1!$I$22:$IV$22 ))
TotalMembers =OFFSET($j$23,0,0,1,MATCH(1E+306,$I$23:$IV$23 ))
Mydates =OFFSET(Sheet1!$J$20,0,0,1,MATCH(1E+306,Sheet1!$I$20:$IV$20))
NewMembers =OFFSET(Sheet1!$J$24,0,0,1,MATCH(1E+306,Sheet1!$I$24:$IV$24))

After looking at your names formulas, it occurred to me that you want to chart only the 7 last dates:

ActiveMembers7 =OFFSET(Sheet1!$B$19,2,MATCH(9^9,Sheet1!$B$19:$IV$19,1)-1,1,-7)
mydates =OFFSET(Sheet1!$B$19,1,MATCH(9^9,Sheet1!$B$19:$IV$19,1)-1,1,-7)

Thanks pistulka

works a treat... could you please explain though the 9^9......

rgds

Any very large number will work.
"9^9" represents a very large number (387420489). You want the match function to not find a match and go all the way to the end of the row which is the largest number (the numbers must be in ascending order, which they are) . The 1 after the array in the match function tells match to find the first value which is equal to or greater than the lookup value. It will always be, in this case, the last column.

pistulka

thanks you for your help on this....

just wondering how do I amend this to read data that fields (active, inactive etc) are displayed on the (X Axis) and the count is on the y axis.
I have played and can't figure it out... BTW the data needs to remain in the format in the attached file

I am not sure what you are asking. I added the dynamic formulas to the chart you had at the bottom. Is that what you need?
data-ange-for-horizontal-dynamic-charts-dynamichorizontalgraph-chart.xlsx

pistulka

Thanks for your patience....

please see sheet 2 in the attached file - I don't think I have been clear for you... this is the format of my data and how I need to have the chart represented...

rgds

1. Charts the last 3 months

2. Remember to enter the next number (i.e. 19)
when entering data

3. I put it on a separate sheet so as not to confuse the names
dynamic_chart.xlsx

pistulka

....thanks so much... I will let you know how it goes within my report....

makes sense when you can see how it's done....

S