Dynamic Charts

[nawin83]

Hi All,


Could you please some one help me on below formula to dymic once of my chart.


I have series of Dates in column B (But i want to consider only from Row B131 to till end), when i use the below formula it is ignoring the last row of the Column B.


Ex: I have data from B1 to B200, as per the below formula it is considering the date from B131 to B199 only. I dont know why it is not considering the last row.


=OFFSET(Sheet1!$B$131,0,0,COUNTA(Sheet1!$B:$B)-130,-1)


Regards,

Nawin

 

[Luke M]

Formula is working okay in my workbook. =/

Why the -1 in the last arguement?


Do you have any blank cells in B1:B200?

 

[nawin83]

Hi Luke,


Thanks for your response.


I tried even 0, 1 in place of "-1", but formual still ignoring the last row.


There is no blank rows in column.


Regards,

Nawin

 

[Luke M]

Hmm. Let's try doing some tests. In helper column (I'll use AA) 1, row 1:

=COUNTA(B$1:B1)

Copy that down to B200


Then, in helper column 2:

=AA1=AA2

Again, copy down as needed. Make sure there are no TRUE values in helper column 2.

THe only thing I can think of is that somehow XL is not counting the cells that it should. Alternatively, change formula to:


=OFFSET(Sheet1!$B$131,0,0,COUNTA(Sheet1!$B$131:$B$200),1)

 

[nawin83]

Hi Luke,


Yes, there are some repeated dates in the column. I have removed those now.


I cannot fix the Column B to till 200, because row keep on increase everyday.


regards,

Nawin

 

[Luke M]

I wasn't checking for repeated dates...we were checking the COUNTA function to see when it messed up the count.

 

[NARAYANK991]

Hi ,


Can you try the following ?


In any unused cell , enter the formula :


=ROWS(OFFSET(Sheet1!$B$131,0,0,COUNTA(Sheet1!$B:$B)-130,1))


Place your cursor in this cell , and click on Evaluate Formula ; as you step through the process , you should come to the last stage :


=ROWS($B$131:$B$200)


If you don't , check at which stage things are going wrong.


Narayan