Dynamic Chart in Excel

[krunk]

Hi, i have made a dynamic chart but its not resizing correctly. Currently the font is not legible. I want to resize based on the length of data shown not resize based on the formula since the formula is returning zero. It has to be able to resize based on various lengths of customer accounts by salesperson. Can someone please assist?

[dflak]

Here are a couple of articles that explain the magic behind dynamic charts:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

Normally, the number of rows to return in an offset command is given by COUNTA() minus extra headers, total rows, etc. However, in this case, you have formulas down to row 40. Although nothing is displayed, the results still count as data. The null string is still not nothing.

You could do this by checking the length of the returned data: if the length is zero, then either the cell is blank or it contains the null string. To apply a rule like Len(G3:G40) = 0 normally requires an array formula, however SUMPRODUCT acts like an array formula. So =SUMPRODUCT(1*(LEN(G3:G40)<>0)) gets a count of cells in the range(G3:G40) where the length of the cell contents is zero. I tested this formula in cell J1. I left it there so you can see how it changes when you type in a new sales person. I copied it into the offset command for Plot_Account, so you don't have to keep it.

[krunk]

This works perfectly! thank you so much.