Dynamic chart and shrinking text boxes

Good afternoon all,

I have a dynamic chart, that records the amount of forms processed eac
week. Every Friday, I coallate the statistics for the past week, an
input them in, and the chart then grows by one week. Perfectly norma
stuff, documented regularly.

What I need to know, is, if it is possible to link outside objects t
parts of the chart.

Example. During 02/01/04 and 23/01/04 we were down 1 FTE (full tim
equivalent), and I have a text box between those dates indicating this
The problem is, when I add on a new week (for example, Week Endin
06/02/04) all the dates shift backwards, and I have to resize the tex
box. 

So, is it possible to link the starting end of a box to 1 "gridline
and the ending point of a box to another, so that it would always b
between 02/01/04 and 23/01/04, no matter how big or small the ga
between the two is.

Thanks for any help you can give,

-Bo

--
Message posted from http://www.ExcelForum.com

0
2/3/2004 4:58:55 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
344 Views

Similar Articles

[PageSpeed] 5

Bob -

It's hard to keep a textbox where you want it, because it likes to 
wander. Use a dummy series instead, with X and Y data configured to put 
a label where you want it, and use datalabels on that series. You can 
link a datalabel to the text in a cell, by adding datalabels to the 
series, selecting a label (one click for the labels for the entire 
series, another click for the specific label), pressing = and then 
clicking on the cell with the mouse. If you have a bunch to do, download 
Rob Bovey's Chart labeler (http://appspro or http://bmsltd.co.uk in the 
MVP section) or John Walkenbach's Chart Tools, both free Excel addins 
that apply an entire range of labels to a series.

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

beeawwb < wrote:

> Good afternoon all,
> 
> I have a dynamic chart, that records the amount of forms processed each
> week. Every Friday, I coallate the statistics for the past week, and
> input them in, and the chart then grows by one week. Perfectly normal
> stuff, documented regularly.
> 
> What I need to know, is, if it is possible to link outside objects to
> parts of the chart.
> 
> Example. During 02/01/04 and 23/01/04 we were down 1 FTE (full time
> equivalent), and I have a text box between those dates indicating this.
> The problem is, when I add on a new week (for example, Week Ending
> 06/02/04) all the dates shift backwards, and I have to resize the text
> box. 
> 
> So, is it possible to link the starting end of a box to 1 "gridline"
> and the ending point of a box to another, so that it would always be
> between 02/01/04 and 23/01/04, no matter how big or small the gap
> between the two is.
> 
> Thanks for any help you can give,
> 
> -Bob
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
jonxlmvpNO (4558)
2/3/2004 10:19:18 PM
Yes, I have chart labeller, used it for some ABS charting a while ago
which made a huge difference to me. Unfortunately, I can't (read: don'
particularly want to)  use a dummy chart for labels, as the labels hav
to be actually off of the line graph. These charts get printed weekl
as transparencies, and they're already cluttered enough, so the label
are underneath the X-Axis. I honestly didn't think there was any way t
do what I want, but thought I'd ask anyway. I've learned so muc
already using this NG / Forums, it's great opportunities to learn wha
Excel can and can't do.

Thanks anyway. :)

-Bo

--
Message posted from http://www.ExcelForum.com

0
2/3/2004 11:27:05 PM
Bob -

Another way to indicate the time period is to use a column chart which 
shows up on those dates but is zero outside of them. Make the columns as 
wide as the space between them (gap width on the options tab = 0), and 
use a transparent rectangle as the custom pattern for the series (see 
http://www.j-walk.com/ss/excel/usertips/tip071.htm).

But you don't want another series for labels, so you probably don't want 
one for this either. Maybe then a macro to relocate the text boxes.

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

beeawwb < wrote:

> Yes, I have chart labeller, used it for some ABS charting a while ago,
> which made a huge difference to me. Unfortunately, I can't (read: don't
> particularly want to)  use a dummy chart for labels, as the labels have
> to be actually off of the line graph. These charts get printed weekly
> as transparencies, and they're already cluttered enough, so the labels
> are underneath the X-Axis. I honestly didn't think there was any way to
> do what I want, but thought I'd ask anyway. I've learned so much
> already using this NG / Forums, it's great opportunities to learn what
> Excel can and can't do.
> 
> Thanks anyway. :)
> 
> -Bob
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
jonxlmvpNO (4558)
2/4/2004 1:22:42 PM
Reply:

Similar Artilces:

Value Limits In A chart
Is there any way possible to change the color of a data series within a chart when it exceeds a value of another data series? Thanks!! Hi see: http://peltiertech.com/Excel/Charts/format.html#CondChart and http://peltiertech.com/Excel/Charts/format.html#CondChart2 -- Regards Frank Kabel Frankfurt, Germany "Robby" <Robby@discussions.microsoft.com> schrieb im Newsbeitrag news:03E95BC9-B246-41E9-A941-177C2C22CB4F@microsoft.com... > Is there any way possible to change the color of a data series within a chart > when it exceeds a value of another data series? > > Tha...

Combo Box to display only "active" records
Hi, I have a combo box based on a list of records in table. Overtime I may want to "de-activate" (Employee.Active=No) records in the list so that a user can longer chose it from the list, but I don't want previously selected records from list to be affected. I added a WHERE Employee.Active=Yes condition to the row source. My drop-down list no longer displays inactive records, but when I look at previous entries the record displayed is also blank. How do I only get this to apply to new records being created? Thanks Yes, that's a common scenario, Nancy. One ...

Data markers in a stacked chart will not sit side by side by month #2
How can I get the monthly columns (ie: July Actual & July Budget) in a stacked chart to sit side by side with a space before the next month (August Actual & August Budget) and so on for each month. ...

Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...

Compare text in 2 different files
I am using Excel 2003 and have two files containing mailing information. I need to check and see which companies are on both lists. I can't use a VLOOKUP formula because some of the company names are slightly different. e.g. One may have a LLC or Inc. at the end and the other doesn't. Is there a formula I can use to compare the first 4 or 5 letters in the company name between the two files? Thanks! Paige Assume you're 2 lists are in A1:A10 and B1:B10 (change to fit your data). Also change the 4 in the LEFT() function to be the number of characters you want to com...

Pivot Chart in Excel 97
Hi guys, i was wondering if anyone knows a way to provide a dynamic chart based on a pivot table with similar functionality to the pivot chart in Excel 2000 or newer? Cheers The Frog *** BUMP *** 24hrs Bump has no meaning in newsgroups. Mr.Frog.to.you@googlemail.com wrote: > *** BUMP *** 24hrs It got your attention Bob :-) Mr.Frog.to.you@googlemail.com wrote: > It got your attention Bob :-) Yes, but what was your orignal question? If you had quoted it in your "bump" you had at least a chance of an answer. But this is a newsgroup and folks don't generally kee...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Deleting Check Boxes
I have a series of 52 check boxes in 52 Rows, I now want to delete all of them. I've tried deleting the column, but the check boxes don't actually disappear. How do I delete them all without having to go into each one and deleting them. I've tried Edit-Go To-Special-Objects, but I've other Check boxes in the same sheet which I don't want to delete. Also how can I uncheck all 52 check boxes (that have ticks in them) in one procedure Thanks Hi John, If by 'procedure' you mean VBA, then to untick the check boxes, try: '=============>> Sub Untic...

Chart tabs changing by themself
I have an Excel 2007 spreadheet (.xlsx) file with about 40 charts all in their own sheets. Every one of those sheets used to have their own descriptive tab name. Excel does not seem to like that, however, as sometimes after closing, it just deletes all of my tab names replacing them with a generic Chart3, Chart4, Chart 5 etc. This is highly frustrating of course. I once edited every one of those tab names back to their original names, wasting a lot of my time in the process, hoping it would not happen again. Alas, it sporadically happens again and again... Any ideas/solu...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Missing text in OL2003 email messages.
The e-mail sender, recipient, and subject is viewable but the contents of the message simply don't show up. ...

how can i auto text in excel
-- how can i auto text in excel Sue I assume you mean that you want AutoComplete. Go to Tools, Options, Edit and select the box at the bottom: Enable AutoComplete for Cell Values. To set up things to replace (for example I have my version replacing my initials with my name), go to Tools, AutoCorrect Options and set things up in there. HTH David "Sue" wrote: > > -- how can i auto text in excel > Sue ...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

Combining multiple Stacked Charts
I have 3 scenarios with info on Asset A and Asset B for 10 years. I can create stack charts for each scenario independently, but can not figure out how to combine all 3 onto 1 chart across multiple years. x axis is year and each year should have 3 stacked lines Y Axis should be total value of both Assets Any help appreciated Does this help? http://peltiertech.com/WordPress/2008/05/19/clustered-stacked-column-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JANeyer" <...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

Using Formulas with Charts
I have a chart that references cells with the following formula =if(a1="","",a1/a2). The formula is used for each month Jan-Dec. I would like to copy the formula across all months. When I do this my data point show as zero for months that have not happened yet. What can I do to not show a line on the chart for a data point with a "" or null value? Thank you, Sandy The cell with the formula isn't blank, and "" does not produce a blank. There is not a formulaic way to simulate a blank, but for many cases, a line chart or XY chart will treat...

countin and updating numbric and text
I need to update a number cell from a text filed. Cell a3 represent the total years of participation. Cellsb3,d3,e3 ECT represent the current year of activity by a “y”. I am trying to update the number in a3 – 5- to 6 or 7 or 8 based on whether or not there is a “y” in b3,d3,e3 ECT. Example a3=5 B3- blank C3 = “y” would update a3 to 6 D3 blank E3 = “y” would update b3 to7 I have tried a number of count and countif and countifs and always receive the value error because of the “y” Can anyone help? Thanks Dick Try: =COUNTIF(B3:Z3,"y") Tom "Anvil22&q...

Area Charts
I have four lines that each define a horizontal zone. When I change each to an Area Chart, it appears that the color is only painted above each line and the color extends from the line all the way to the top of the chart. But above the top line I'd like this to be uncolored. I want the top two areas are filled in BELOW each line to a horiz. mid-point on the chart and the bottom two areas are filled in ABOVE these lines to a horiz. mid-point. In effect I want the chart to depict from top to bottom: Empty space Danger zone Safe zone Safe zone Danger zone Empty space Help!! Cr...

Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...

Extract numbers from a string of text
Need to extract just the numbers at then end of the string. 1300 · Inventory:1305.05 · Inventory Business to Business:1305.10 · B2B Inventory Contra 1300 · Inventory:1305.05 · Inventory Business to Business:1305.15 · B2B Inventory 1300 · Inventory:1310 · Inventory - Raw Materials:1310.05 · Inventory - Flavoring 1300 · Inventory:1310 · Inventory - Raw Materials:1310.10 · Inventory - Green Beans 1300 · Inventory:1310 · Inventory - Raw Materials:1310.15 · Loose Tea 1300 · Inventory:1320 · Invent...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

Page header and footer for charts from my template
I plot a lot of Y vs X data as scatter plots. Usually, I want the graphs as separate sheets. For these sheets, I want a standardized header and footer. Presently, I do this manually by 1. Select the chart location as New sheet, with a name 2. In the new sheet, click the Print Preview button 3. Click Setup... 4. enter Custom Header and Custom Footer data. I always enter the same data in step 4 above. It should be ideal for a template. Is there a way to use a template for this? Next time you add charts, assuming you add them to end of your workbook, click the last sheet that already...

Mail Merge
Hi I am the admin on the crm 4.0 environment here. Whenever i do a mail merge through outlook or through the web client i dont get the create activites dialog box. Instead i always get a pop up saying "Opening this document will run a SQL command and data from the database will be placed into your document" When i click yes to continue , I get a pop saying mail merge can not find the data source... and then two options to remove all merge info or remove data header source.. any ideas what i could do to fix this? regards Snow White ...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...