Chart Data Range ...

[synergy46]

I am running XL 08 chart based on 3 columns of data:

****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 2008"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/Ron/Library/Caches/TemporaryItems/msoclip/0/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;} td {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {font-size:12.0pt; font-weight:700; mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; background:silver; mso-pattern:auto none;} .xl25 {mso-number-format:"_\(* \#\,\#\#0\.00_\)\;_\(* \\\(\#\,\#\#0\.00\\\)\;_\(* \0022-\0022??_\)\;_\(\@_\)";} .xl26 {mso-number-format:"\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)";} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse;" width="321" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" width="170"> <col style="" width="76"> <col width="75"> <tbody><tr height="16"> <td class="xl24" width="170" height="16"> Account </td> <td class="xl24" width="76"> Actual </td> <td class="xl24" width="75"> Budget </td> </tr> <tr height="13"> <td class="xl26" height="13">Awards</td> <td class="xl26" x:num="285.0" align="right">285.00 </td> <td class="xl25" x:num="400.0" align="right"> 400.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Degree expense</td> <td class="xl26" x:num="704.0" align="right">704.00 </td> <td class="xl25" x:num="200.0" align="right"> 200.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Demolay</td> <td class="xl26" x:num="199.35" align="right">199.35 </td> <td class="xl25" x:num="200.0" align="right"> 200.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Donation - to MLA</td> <td class="xl26" x:num="77.0" align="right">77.00 </td> <td class="xl25" x:num="150.0" align="right"> 150.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Donations - from member</td> <td class="xl26" x:num="200.0" align="right">200.00 </td> <td class="xl25" x:num="500.0" align="right"> 500.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Donations - from others</td> <td class="xl26" x:num="75.0" align="right">75.00 </td> <td class="xl25" x:num="100.0" align="right"> 100.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">GL Comm Assis.</td> <td class="xl26" x:num="3055.5" align="right">3,055.50 </td> <td class="xl25" x:num="700.0" align="right"> 700.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">GL fees</td> <td class="xl26" x:num="99.0" align="right">99.00 </td> <td class="xl25" x:num="300.0" align="right"> 300.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">IORG</td> <td class="xl26" x:num="50.0" align="right">50.00 </td> <td class="xl25" x:num="200.0" align="right"> 200.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">JDI</td> <td class="xl26" x:num="333.0" align="right">333.00 </td> <td class="xl25" x:num="222.0" align="right"> 222.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Meals - Regular</td> <td class="xl26" x:num="1000.0" align="right">1,000.00 </td> <td class="xl25" x:num="1200.0" align="right"> 1,200.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Meals - Special</td> <td class="xl26" x:num="878.0" align="right">878.00 </td> <td class="xl25" x:num="500.0" align="right"> 500.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Office Supplies</td> <td class="xl26" x:num="987.0" align="right">987.00 </td> <td class="xl25" x:num="1200.0" align="right"> 1,200.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Postage</td> <td class="xl26" x:num="74.0" align="right">74.00 </td> <td class="xl25" x:num="500.0" align="right"> 500.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">Rent</td> <td class="xl26" x:num="200.0" align="right">200.00 </td> <td class="xl25" x:num="1400.0" align="right"> 1,400.00 </td> </tr> <tr height="13"> <td class="xl26" height="13">:qqq</td> <td class="xl26" x:num="33.0" align="right">33.00 </td> <td class="xl25" x:num="400.0" align="right"> 400.00 </td> </tr> <!--EndFragment--> </tbody></table>
These three columns are dynamically named and confirmed to include all rows up to Rent (2nd from the bottom).
The chart looks good. Then I add, in the source Table an entry for qqq. The Dynamic Range recognizes the new qqq
row and includes it. But, the chart DOES NOT recognize the expanded Dynamic Named Range. Instead it
continues to show the old range. This requires me to MANUALLY readjust the Data Range everytime I update the Accounts.

a) Is there some 'trick' to inputting a dynamic range name as a data source? (Just typing it in
preceded by = doesn't work. OK, it works until the data set changes then it keeps the old data range)

b) I tried typing the dynamic range name into the =series for each data set but that just hung Excel...

Ideas appreciated...

 

[taigovinda]

Hi!

You can't use it in the 'Chart Area' because it will just evaluate once and then become an absolute reference within the chart... You need a separate dynamic range for each series, so three names and not just one.

 

[synergy46]

EXACTLY what I needed to hear. Your words, tripped an understanding that I had not previously gotten from my 4 Excel books.

Applying a dynamic range to each data series plus the dynamic name for the account titles did the trick.

Thank you verrrrrrry much! I wish there was some way to give you 'points' or recognition for the insight.

Ron

 

[taigovinda]

Glad it worked for you, Ron... but if there were a point system I'd undoubtedly be way in negative territory from all the help I've received on here!

Tai