Dynamic Chart Scaling Error

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have finally figured out how to dynamically scale a chart that links to a table that can change by have the trailing cells empty. <br><br>The Table has two rows and 16 columns C6:R6 are dates and C7:R7 are numbers. When any of the cells at the end of the table are blank such as Q6 and R6 being blank I want the chart to only plot 14 data points. I should add C6:R7 are all formulas that reference another place in the work book. <br><br>That I did is define names Where the name &quot;NBCL&quot; is =OFFSET(Sheet1!$C$7,0,0,1,COUNT(Sheet1!$C$7:$R$7)) <br>
and &quot;DATUP&quot; is =OFFSET(Sheet1!$C$6,0,0,1,COUNT(Sheet1!$C$6:$R$6)) <br><br>I then write the Series equations as: <br>
=SERIES(,'Scaling experiment.xlsx'!DATUP,'Scaling experiment.xlsx'!NBCL,1)  <br><br>It works great except if all the data is removed from the table and then put back the Chart will not plot and I get the message &quot;Your formula contains an invalid external reference to a worksheet&quot;. When I save the workbook, close it, and reopen it the chart now plots correctly again. <br><br>Why series equation never changes, so why does it do this?
0
robbbo
5/10/2010 1:30:14 AM
mac.office.excel 1146 articles. 0 followers. Follow

0 Replies
696 Views

Similar Articles

[PageSpeed] 26

Reply:

Similar Artilces:

Excel Scale to Fit problem
I have two computers, both with Vista, both with the same printer driver and version of Excel. When one user opens the file and prints the page breaks at row 66. When the other user prints the file it breaks at row 68. The file is set to a scale to fit of 65%. If the second user changes the scaling to 67% the same number of rows print. What factors influence the scaling. Like I stated above, same OS, same font (even check versions), same Excel, same printer and driver. -- Tim I have made no progress on this, has anyone else seen this problem? -- Tim "tboggs13&q...

http_403 error
This has been cross posted in the windows.sbs news group. Ok, I am going nuts here, can someone help me? I'm using Microsoft SBS 2003. I get a 403 error from any device trying to sync. I have checked all the VD's and they seem fine. Here is the IIS log: 2005-08-23 02:57:07 203.79.110.126 OPTIONS /Microsoft-Server-ActiveSync User=neill&DeviceId=B77507360000000108000050BF3F5173&DeviceType=PocketPC 80 - 166.179.17.182 Microsoft-PocketPC/3.0 401 2 2148074254 2005-08-23 02:57:09 203.79.110.126 OPTIONS /Microsoft-Server-ActiveSync User=neill&DeviceId=B77507360000000108000050B...

Dynamic Days Of Month
I am building some automated reports based on the values in a linked DBase Table. The DBase columns are labeled "ONE" thru "THIRTYONE" for each day of the month. The values returned for each day are either 0, 100 or null. I am trying to differentiate between a null because the day is in the future and a null because there was no value entered that day. I thought if there was some way to relate each of the DBase columns to a separate table that had a true date value for each day of the month I could build an expression to return two different strings for the null b...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

How to Arrange Data for Chart
I charted the following data in a clustered column chart. Level 1 Level 2 Level 3 Level 4 FY07 - Total 167 23 20 43 81 FY08 - Total 178 22 40 26 90 These are total department errors segregated by levels for each fiscal year. There are 3 different departments involved. What I did was just total the errors for each FY and each level. Now my boss wants to see how many errors were performed within each level by Dept A, Dept B and Dept C and compare the 2 fiscal years. I hope I’m explaining...

Encryption error
When I open Money 2004 I get the following error "Unable to encrypt data at the required level" Tells me to go to a site and install some security updates, which I did. I have all the latest patches and updates (except XPSP2, it sucks). Have latest IE 6 with SP1 and latest encryption pack. Help! In microsoft.public.money, "Encryption error..." <Encryption error...@discussions.microsoft.com> wrote: >When I open Money 2004 I get the following error > >"Unable to encrypt data at the required level" > >Tells me to go to a site and install so...

Circular Reference Error
Can anyone see what would be causing me to get a circular reference error??? Thanks! SELECT [BartS1Report].Customer_ID, [BartS1Report].Customer, [BartS1Report].Service_Address, [BartS1Report].Employee, [BartS1Report].Manager, [BartS1Report].Type_of_System, [BartS1Report].Raw_Water, [BartS1Report].Treated_Water, [BartS1Report].Cycles, [BartS1Report].Inhibitor_Level, [BartS1Report].Range_1, [BartS1Report].Range_2, [BartS1Report].Range_3, [BartS1Report].Range_4, [BartS1Report].Range_5, ((SELECT Sum([XX].[ZZZ]) FROM [BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID & ...

Conditional formating of Charts
Hi, I have a scatter chart and need to conditionally format points and associated labels for each point. The condition is not related to the data series to which the chart is linked. Let me know if there is any specific VBA code for doing the same. You can reach me at krishna.guha@citigroup.com There are some non-VBA suggestions here: http://peltiertech.com/Excel/Charts/ConditionalChart1.html To change some points of a series or some series in a chart based on a known criterion, you can adapt one of the macros in this post: http://www.google.com/groups?selm=3F4B8D49.3080508%40yah...

Merging Charts
Is there a way that I can merge Excel information into Publisher as a graph specific to each entry. I am trying to take several peices of information specific to a variety of people and merge that information into a document, making make pie charts for some of that information for each person. ...

Autofill Error
Hi, I need a macro to autofill COLUMN A with a word say YES until the last row found in COLUMN B. Right now I have the ff codes: Sheets("VOUCHER - STEP 2").Select Range("A5").Select ActiveCell.FormulaR1C1 = "DEBIT" With ActiveCell.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineSt...

graphs charts
Can I decide what the graph scale can be and be constant for all the graphs I want to make for excel data In article <09F59A13-D91B-4114-8155-815B0135155B@microsoft.com>, MICKO@discussions.microsoft.com says... > Can I decide what the graph scale can be and be constant for all the graphs I > want to make for excel data > Assuming the axis of interest supports numeric data, double click the axis then select the Scale tab. The rest should be self-evident. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office producti...

Color transitions in a chart
Hi I am creating a chart to plot some values but would like to have the background of the chart to start red in the upper right, transition to yellow around the middle and then green in lower left. The values I have represent High, Medium and Low risk items and would like the chart to represent these with the background color when I plot them. Hope this makes sense. I appreciate if you have any suggestions. Thanks In article <089EA19B-10D5-4AA4-8B34-D57CA46C0007@microsoft.com>, Sboone wrote: > Hi I am creating a chart to plot some values but would like to have the ...

excel charts and graphs
I want to assemble several pages of excel charts and graph in the form of a poster without printing them out. THOR ...

XL2007
I have a spreadsheet that uses the following sequence to scale the y axis of an X-Y scatter graph with lines drawn between points from VBA and it works perfectly in all previous versions of Excel. It fails with XL2007 rather catastrophically. The main spreadsheet was imported from XL2003. I have looked in the KB and archives here and not found any other reports of this problem so I don't know if it is something peculiar about this particular installation or what. It is reproducible in a toy chart in pure XL2007. The failing lines are; ActiveChart.Axes(xlSecondary).Select ok = Applicati...

Mail error
I have been using outlook for a week and it was working fine all of a sudden i cannot retreive any emails, i can send but not receieve in outlook. i have a Hotmail plus account the service status in oultook says Mail: error Calender: connected Contact: connected can you help me with that?? ...

2003 code raises error in 2007
I have a reference in VBA to a subform of a subform, which works fine in Access 2003. In Access 2007, however, it results in "you have entered an invalid reference to the property Form/Report." This is the snippet of code: Private Sub SetFormProperties() 'called from form_open, makes all forms editable if edit mode was selected Dim ctrl As Control, ctrlSub As Control Dim frm As Form If Me.OpenArgs = "Edit" Then Set frm = Me 'set main form edit options EditProperties frm 'set subform edit options For Each ctrl In frm If ct...

Outlook errors #4
Since upgrading to outlook 2003 I cannot view any items in the sent folder. I have tried uninstalling and reinstalling but get the same error- "outlook has encounted an error and will close" I can view the sent items via OWA, so I know they exist. I have also tried setting up the account on a different pc. Any help would be very helpful. Have you tried a new mail profile? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without ...

No updating of Excel chart
Hi. Is there any way to prevent excel from updating charts? I'm having some problems with one chart when I try to unhide or delete some rows that ARE NOT inside the series data, but the chart allways keep wrongly changing the data rows. When i try to unhide the rows, the series expands to the number os rows that was unhided. When i try to delete the rows, the series all became #REF's. Repeating: the rows that i need to delete are CONTIGUOUS to the data series and ARE NOT inside the series. I've checked this thoroughly. If someone have any ideas, I would be immense...

Pivot Chart Filter
I have two Pivot charts that compare similar data and have them displayed side-by-side. When I change the filters in one chart, I want to apply the same filter for the other chart. Is there a way to have one Pivot Chart Filter Pane control two or more Pivot Charts without having to click each chart and change to filter for each one? ...

Display of 3D bar charts...looking jagged??
Is there anyway to get around the way excel displays 3D bar charts.....they look the edges of the bars are not smooth they look like a child drew them with broken lines....I cut and paste these charts into Powerpoint and my boss presents these presentations to clients via email attachments and the clients get to see how rough the bars are.....any ideas? Thanks in advance...oh, and good working with you all! Do you make the charts in a new work sheet ? I never had that issue in xls, but when copying too small pics/charts into ppt, they will be distorted when resized. Using a new worksh...

"Get-ClusterResource" cmdlet throws "not recognized as a cmdlet, function, operable program, or script file" error.
Hi, I am trying to use "Get-ClusterResource" cmdlet to fetch "File Share Witness" property of CCR cluster. But when I run this command no "Exchange Management Shell" I am getting "The term 'Get- ClusterResource' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again." error. Any idea why I am getting this error? Do I need to add any "PSSnapin" via Add-PSSnapin cmdlet ? If so which one it is? Basically I am not able to run any of the cluster related GET cmdlets on Exchange Managem...

E2k3: An ambiguous SMTP proxy SystemMailbox error 9549
I get this message a few times a day and the best I can figure is I have two mailboxes found in the Exchange System Manager with the same string of characters in their names. SMTP (SERVER1-{A643FE61-xxxx-4B74-B902-280BFB58699B}) SystemMailbox{A643FE61-xxxx-4B74-B902-280BFB58699B} However this condition is identical on two exchange servers (slightly different numbers) but only one exchange server is getting the error. Any ideas? Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9549 Date: 1/9/2005 Time: 2:28:44 PM User: N/A Computer: SERVFL01 Description: An...

Urgent
Win XP HE, XL 2002 SP3 Hi, I am doing a rush presentation and am stuck...! Under Tools|Options| Chart tab, the otion to "Plot empty cells as: Not plotted" is grayed out. Yet, that is precisely what I need to do. How can I fix that? I have other charts where the optin is available. What am I doing wrong? S I found out the following: I had an "area chart" which (by definition I guess) will build the area from "0". Changing it to a line chart, did the trick. S saturnin02 wrote: > Win XP HE, XL 2002 SP3 > Hi, > I am doing a rush presentation and am stuck...

Error sending email and editing email's body
I have the following function Private Sub SendEmail_Click() Dim strSubject As String Dim strEmailList As String Dim strMessage As String Dim strMessage2 As String On Error GoTo Err_SendEmail_Click strSubject = "Please investigate" & " " & [Ticket #] strEmailList = "JohnDoe@emailaddess.com " strMessage = Me.Text_Description strMessage2 = "Assign to:" DoCmd.SendObject acSendNoObject, , acFormatRTF, strEmailList, , , strSubject, strMessage, strMessage2 Exit_SendEmail_Click: Exit Sub Err_SendEmail_Click: ...

Error when drawing grid, grid lines a bit too short
Hello, as I've mentioned in some other post I'm making a simple "Game of Life" with a small, fixed-sized grid. Anyway, the function I used for drawing the actual grid was written ages ago when doing some pure Win32 programming and it was full of magic numbers, assuming a 16*16 grid. I tried to rewrite it without magic numbers and to take the actual size into consideration. Right now each cell in the grid is 20*20 and I have horizontal and vertical bars that are four pixels wide. The x and y offset is 20 pixels. Hope I explained it properly, here's the code: voi...