Chart Source Data Ranges Changing when Data Sheet updated from text file source.

I have a simple Excel wb with 2 sheets.  Sheet 1 is simple Line Charts.
Sheet 2 is the data for these charts.  The data comes from a text file via
the 'Import External Data' tool.
The text file is filled throughout the day with data every 5 minutes
starting at 00:00.  Because the chart will contain 288 data points I've set
my series values to"=<data sheet name>!$C$2:$C$289"

I have 2 questions.
1) Whenever I refresh the Data Sheet from the external text file the series
values in my charts increment by the number of new data points.  This is no
good as it causes my charts to appear differently throughout the day.  What
could be the cause?

2)  Is there a way to chart just cells which have data in them?  If I set
the series values in a chart to "=<data sheet name>!$C:$C" it creates a
chart with 65535 data points all but a few blank and the chart is no usable.

Thanks,
--S


0
nope6520 (2)
1/7/2004 2:53:17 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
487 Views

Similar Articles

[PageSpeed] 27

See example of dynamic chart at www.stfx.ca/people/bliengme/ExcelTips
Best wishes
Bernard

"Tekn0" <nope@notgonnahappen.com> wrote in message
news:vvo7au1h27on84@corp.supernews.com...
> I have a simple Excel wb with 2 sheets.  Sheet 1 is simple Line Charts.
> Sheet 2 is the data for these charts.  The data comes from a text file via
> the 'Import External Data' tool.
> The text file is filled throughout the day with data every 5 minutes
> starting at 00:00.  Because the chart will contain 288 data points I've
set
> my series values to"=<data sheet name>!$C$2:$C$289"
>
> I have 2 questions.
> 1) Whenever I refresh the Data Sheet from the external text file the
series
> values in my charts increment by the number of new data points.  This is
no
> good as it causes my charts to appear differently throughout the day.
What
> could be the cause?
>
> 2)  Is there a way to chart just cells which have data in them?  If I set
> the series values in a chart to "=<data sheet name>!$C:$C" it creates a
> chart with 65535 data points all but a few blank and the chart is no
usable.
>
> Thanks,
> --S
>
>


0
bliengme1 (122)
1/7/2004 3:19:57 PM
1) Won't the charts appear differently as data is appended to the 
series? Or is there a difference I don't understand?

2) You can set up dynamic ranges that contain only the non blank part of 
the data range, and chart these. Here's an overview, with examples and 
more links:

   http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tekn0 wrote:

> I have a simple Excel wb with 2 sheets.  Sheet 1 is simple Line Charts.
> Sheet 2 is the data for these charts.  The data comes from a text file via
> the 'Import External Data' tool.
> The text file is filled throughout the day with data every 5 minutes
> starting at 00:00.  Because the chart will contain 288 data points I've set
> my series values to"=<data sheet name>!$C$2:$C$289"
> 
> I have 2 questions.
> 1) Whenever I refresh the Data Sheet from the external text file the series
> values in my charts increment by the number of new data points.  This is no
> good as it causes my charts to appear differently throughout the day.  What
> could be the cause?
> 
> 2)  Is there a way to chart just cells which have data in them?  If I set
> the series values in a chart to "=<data sheet name>!$C:$C" it creates a
> chart with 65535 data points all but a few blank and the chart is no usable.
> 
> Thanks,
> --S
> 
> 

0
1/7/2004 8:57:01 PM
Sure the chart does change, but what is happening is that because there will
only ever be 288 data points, the refresh of the data adds additional length
to the series values.  The result at the end of the day is a chart of the
days data, that only spans the front half of the chart.

I think your dynamic examples are going to do exactly what I need.  Thanks
for the link.

--S



"Jon Peltier" <jonpeltierNOSPAM@yahoo.com> wrote in message
news:OZ6xJDW1DHA.2888@tk2msftngp13.phx.gbl...
> 1) Won't the charts appear differently as data is appended to the
> series? Or is there a difference I don't understand?
>
> 2) You can set up dynamic ranges that contain only the non blank part of
> the data range, and chart these. Here's an overview, with examples and
> more links:
>
>    http://peltiertech.com/Excel/Charts/Dynamics.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______


0
nope6520 (2)
1/8/2004 4:45:18 PM
Reply:

Similar Artilces:

Steps in Exchange 2000 to change domain name?
We have an existing domain name (company.com). We have decided to change our name to newcompany.com. Right now the users are user@company.com and we want it to be user@newcompany.com. What do I need to do to change that in Exchange? I have made the necessary DNS changes (or so I believe). Thank you!!! Douglas Just add newcompany.com to your recipient policy & make it the default...best not to remove your existing company.com addresses as it may help during the transition to let users receive mail for both addresses. See http://www.msexchange.org/tutorials/MF010.html for help. ; ...

Banking Services Update Problem #2
I have successfully completed all of the steps as provided for in the update instructions. Initially updates were received as usual. However, now all of the accounts that were disabled and set-up again are failing to update. Message received is "Update unsuccessful" and call summary message is "Microsoft Money could not update your account. Please try to update your account again." Also after updating, the accounts that were updated never displayed a password text box. I went through the disable and set-up process again and still no password text box on the upd...

Maximum file sizes
Is there a recommended maximum file size for Excel 2000. PC spec 2Ghz P4 with 256 Mb Any advice appreciated Deus -------------- Does Not Exist Hi have a look at http://www.decisionmodels.com/memlimits.htm -- Regards Frank Kabel Frankfurt, Germany "Deus DNE" <deus.dne@ntlworld.com> schrieb im Newsbeitrag news:1561701c41d4f$358950f0$a001280a@phx.gbl... > Is there a recommended maximum file size for Excel 2000. > > PC spec 2Ghz P4 with 256 Mb > > Any advice appreciated > > Deus > -------------- > Does Not Exist ...

find action on log file
Hello there I want to use outside tool to find who made some update on table in my server I know that there are many tools for this. But can they do it on simple recovery model? Roy Goldhammer (royg@yahoo.com) writes: > I want to use outside tool to find who made some update on table in my > server > > I know that there are many tools for this. But can they do it on simple > recovery model? No. If you are using the simple recovery model, the contents of the log is wasted away everyonce in a while. Well, if the disk area has not been overwritten...

File size #11
I have read the other discussions on file sizes but they do not seem to address my problem. I have an Excel file that is 12mb large with low-res jpegs in it. This file also has merged cells to make it look pretty. Does Excel look at these merged cells as graphics? Is this why they are too big? I have run a macro to make sure that it goes to the last cell. How can I get the file smaller? How big are the graphics? If you remove them from the file, what is the size of the file and what is the size of the graphic files? To be sure you do not have extra formatting, if you open the file...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

OL2007 not move big files from outbox to sent
Hi, We have 2 computers with separate email accounts on Roadrunner. One machine has XP with Outlook 2002-sp3 and works without any problems. The other has Outlook 2007 on Vista and has problems sending files over a meg or so in size. It seems to actually send the file but the file remains in the outbox folder and does not move it to the sent folder. I say it "seems" to send the file because some people complain of getting muliple copies and others don't seem to get them at all. If I hit send again (not set up for auto send) it seems to send the file again (why some ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

How to repair a .dll file in IE8
Several days ago I noticed in my Dependency Walker that the IESHIMS.dll files has a yellow circle with a question mark on it. What does this mean and How do I repair it? OS: Windows Vista Home Premium Browser Internet Explorer 8 -- TW Hi, See the History tab on that dialog. A web search for ieshims.dll files will also help you find a solution for that file. Regards. "TW" <TW@discussions.microsoft.com> wrote in message news:63E61463-D766-4ABC-B081-BFA8C04FB159@microsoft.com... > Several days ago I noticed in my Dependency Walker that the IESHIMS....

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

unsolicited entry in the folder "Temporary Internet Files"
Hello, I am working on a programme which browses web sites and runs under XP. The http download is as follows: pServer = Isession -> GetHttpConnection(strServerName, nPort); pFile = pServer->OpenRequest(CHttpConnection::HTTP_VERB_GET, strObject, NULL, 1, NULL, NULL, dwHttpRequestFlags); pFile->SendRequest(); pFile->QueryInfoStatusCode(dwStatusCode); if(dwStatusCode == 200) { pFile -> QueryInfo(HTTP_QUERY_LAST_MODIFIED, &sysT); status.lastMod = sysT; if(DBlastMod == status.lastMod) //URL content has not changed since the last visit ...

unable to paste Excel 2003 chart into Outlook 2003
(This was posted on "excel.charting" group.) I have a user who's unable to paste an Excel 2003 chart into Outlook 2003 email message. In Outlook options, the checkbox is selected for "Use Microsoft Office Word 2003 to edit e-mail messages". When I tested this on my own computer running the same version of Office, if the box is check, I have no problem pasting; if this box is cleared, I cannot paste. But on his computer, it doesn't work regardless. Thanks and regards, TL ...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

Change position ID in HR
We would like to change the position ID in human resources. Does anyone have a suggestion on this. You would need to do it behind the scenes using a tool like Query Analyzer. -- Charles Allen, MVP "KT" wrote: > We would like to change the position ID in human resources. Does anyone have > a suggestion on this. careful though when you change it on the background as you need to know all the tables that use this position ID or Position Code and change it there too otherwise all the link would be gone and you end up with orphan records that its just the same as creatin...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

Setting a dynamic range in a formula
Hi, I have a column of numbers and I always want the following arra formula to use the last 12 entries: =(PRODUCT(1+D1:D12/100)-1)*100 Any suggestions? Thanks, Phillycheese -- Phillycheese ----------------------------------------------------------------------- Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=37809 Assuming that Column D contains no blanks, try... =(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100 ...confirmed with CONTROL+SHIFT+ENTER. Hope th...

Problem with Range
Hello All, Using Windows & Excel XP. I have a worksheet that has times located in every other column, A1:A30, C1:C30, E1:E30. I then name the range "times". I want to find the count of times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes). I write the formula: =COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59)) but get the error #VALUE! I have tried writing a formula for times in one column and consecutive columns and it gives the correct count, it is just when the times in every other column that th...

Average of absolute values of moving ranges
I'm trying to get the average of the absolute values of a set of data over 8 weeks. Each week is on a seaparate sheet so to capture the moving ranges I've been using the formula below to get my result. Is there an easier way? =AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2)) Thanks! Amy The use of t...

CSV Files and VLOOKUP error
Does anyone know why VLOOKUP and Compare formulas don't work o information originating from a CSV file? I've tried copying an pasting values only (to leave behind any formatting), but it doesn' help. Through countless tests, I've narrowed it down to the CSV file bein the only possible cause -- Message posted from http://www.ExcelForum.com Hi ajpowers, Just a guess but the imported data may have leading or trailing spaces or are numbers stored as text. You could use the formula =A1=D1 to see if you get a true or false, where A1 is the lookup value and D1 ia the CVS valu...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

AR invoicing update GL but not receivables
We have an issue where the GL is updated with invoicing activity, but in some cases the receivables side is not. Any piror issues with this, advice on how to figure out the problem, etc? Thanks I have only heard of the opposite happening - AR subledger is updated, but the GL is not. Can you walk through exactly what happens? What type of AR document in what screen? What is the 'On Account' amount? What are the GL distributions? Where are you going to see that the AR subledger is not updated? Where do you see that the GL is updated? -- Victoria Yudin Microsoft MVP - Gre...

Excel corrupts when asking to update vlookups
We are experiencing weird behavior with some Office 2K3 Excel spreadsheets that contain lots of calculations, but no macros. On some pc’s Excel acts normally, on others you get the error. I have a couple of screen shots available. Any help is appreciated. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Gu...

How do I overlay text to a row without loosing the text in the ba.
I would like to know how to give an entire row (or column) a text overlay such as "VOID" and still be able to view the text in the underlaying row (or column). Thanks in advance. Use WordArt from the Drawing toolbar. Change the Fill to None. -- Jim Rech Excel MVP "Bruce Charles" <Bruce Charles@discussions.microsoft.com> wrote in message news:C430F6BC-1EBD-461F-A3FA-EC8592C5704C@microsoft.com... |I would like to know how to give an entire row (or column) a text overlay | such as "VOID" and still be able to view the text in the underlaying row (or | c...