Pick up date for Charts from the Spreadsheet

I have a workbook with one data sheet and several charts which represent 
weekly activity.  I want to be able to pick up the title of the charts from 
the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006".  This 
title information is contained in a cell in the spreadsheet.

How do I get it to display at the top of each chart without having to 
manually type it in the title area under Chart Options?



0
wpla (55)
4/5/2006 4:44:46 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
420 Views

Similar Articles

[PageSpeed] 29

Wilfred:

Here' a link to a post I have on dynamic titles that sounds like what you 
are looking for.

http://processtrends.com/pg_chart_dynamic_chart_title.htm

I  use  a text box and link the box to the chart by activating chart, 
entering "=" in formula bar then pointing to source cell.

You can format the text and place where you want on the chart. As an aside, 
I prefer this text box approach to using Excel's chart title option. I find 
the chart title option takes up more space than is necessary for the title.

As the source cell is updated, the title will automatically be updated.

One last point, you can use a concatenation formula to make your source cell 
update as your week changes.

Your source title could be  = "Sales Activity for Week Ending " & 
Text(A1,"mmmm d, yyyy"). Substitute the actual date cell for A1 in this 
example.


...Kelly

koday@processtrends.com



"Wilfred" <wpla@hotmail.com> wrote in message 
news:IYadnbG_zMRibq7ZnZ2dnUVZ_vednZ2d@comcast.com...
>I have a workbook with one data sheet and several charts which represent 
>weekly activity.  I want to be able to pick up the title of the charts from 
>the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006".  This 
>title information is contained in a cell in the spreadsheet.
>
> How do I get it to display at the top of each chart without having to 
> manually type it in the title area under Chart Options?
>
>
> 


0
dkod (205)
4/5/2006 5:13:42 PM
Kelly O'Day, I have three words for you:  Awesome, awesome, awesome!!!

Sometimes I post to these newsgroups and get so many tangential replies that 
I wonder if it's worth the effort of composing a question.  Your answer was 
1000% on the mark!  Thank you so much.  Thanks also for the example.  I've 
used the Text formula before but I always have to do a lot of trial and 
error before I can get it to format the dates exactly the way I want it.

Your reply is perfect!


"Kelly O'Day" <dkod@comcast.net> wrote in message 
news:%23YbdKRNWGHA.4848@TK2MSFTNGP03.phx.gbl...
> Wilfred:
>
> Here' a link to a post I have on dynamic titles that sounds like what you 
> are looking for.
>
> http://processtrends.com/pg_chart_dynamic_chart_title.htm
>
> I  use  a text box and link the box to the chart by activating chart, 
> entering "=" in formula bar then pointing to source cell.
>
> You can format the text and place where you want on the chart. As an 
> aside, I prefer this text box approach to using Excel's chart title 
> option. I find the chart title option takes up more space than is 
> necessary for the title.
>
> As the source cell is updated, the title will automatically be updated.
>
> One last point, you can use a concatenation formula to make your source 
> cell update as your week changes.
>
> Your source title could be  = "Sales Activity for Week Ending " & 
> Text(A1,"mmmm d, yyyy"). Substitute the actual date cell for A1 in this 
> example.
>
>
> ..Kelly
>
> koday@processtrends.com
>
>
>
> "Wilfred" <wpla@hotmail.com> wrote in message 
> news:IYadnbG_zMRibq7ZnZ2dnUVZ_vednZ2d@comcast.com...
>>I have a workbook with one data sheet and several charts which represent 
>>weekly activity.  I want to be able to pick up the title of the charts 
>>from the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006". 
>>This title information is contained in a cell in the spreadsheet.
>>
>> How do I get it to display at the top of each chart without having to 
>> manually type it in the title area under Chart Options?
>>
>>
>>
>
> 


0
wpla (55)
4/5/2006 7:03:30 PM
Reply:

Similar Artilces:

date flashing
hi, i am asking if you can, if so how the date flashes up in excel. in example if u have a load of cars when there mots are due and you have all the dates in, and i want it to flash up a colour(red) when it reaches that date so you know when to mot it. -- gummertwo ------------------------------------------------------------------------ gummertwo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31741 View this thread: http://www.excelforum.com/showthread.php?threadid=514540 Take a look at Conditional Formatting under Format. HTH Regards, Howard "gummer...

Date and time #3
Can I have excel display the current date and time? Time is not tha important but date would be great -- Message posted from http://www.ExcelForum.com =TODAY() will update with every calculation or manually pressing F9 =NOW() date and time -- Regards, Peo Sjoblom "gsaenz >" <<gsaenz.103553@excelforum-nospam.com> wrote in message news:gsaenz.103553@excelforum-nospam.com... > Can I have excel display the current date and time? Time is not that > important but date would be great. > > > --- > Message posted from http://www.ExcelForum.com/ > ...

Drawing a line on a chart
Help! I need to draw a line on a chart that is horizontal - - in old Excel, the SHIFT key could be used to "snap to" the horizontal, but in Excel 2007, it does not work and it is driving me crazy! Not sure what you mean by 'snap to' Holding SHIFT while drawing the line will keep it horizontal but I cannot recall in Excel2003 that you were able to fix a line that you had already drawn - my PC will XL2003 is out of commission today so I cannot confirm this. I have just experimented with XL2007 : holding SHIFT does indeed keep the line horizontal Try again on ...

Chart from separate workbooks
Hello! I`m new to this group so greetings for everybody :). My questions (MS Excel 2007): There are 10 separate files, each containing a similar worksheet. There is an eleventh, cumulative file, containing a chart from these ten files. The number of files grows constantly. I would like the charts in the cumulative file to change, based on the newly created files. How to do this in a possibly automatic manner? For example: 5 persons are evaluated each month with a 10-question test and get a grade (from 1 to 5)for each question. In the cumulative file there is a chart depicting the mea...

user defined scale on chart
Hi, is there a way to have a non continuous scala on a excel chart ? I have a financial chart that shows expenses from 100.000 up t 14.000.000. Since the 100.000 is so small its hardly visible on th chart (min : 0 / max: 16.000.000 ) I use major unit : 2.000.000 so the chart would display the axis scal 0 / 2Mil / 4 Mil / 6 Mil / 8 Mil / up to 16Mil. Since most of my datas are around 2 - 4 Mil. the 14 Million reall stand out on the chart are make the low numbers almost disappear. Is there a way to define the scale that the chart would show on th axis 0 / 2 / 4 / 6 Million and then the ne...

Variable series multi line chart?
I have a set of records each having a month/yr value, a product value and a number field called Backlog that is either 1 or 0. There will always 24 month/yr values represented in my data (24 month range) and there can be at least 40 different products. The month/yr values will change depending on what month my report is run, but there could be any number of products. I want a multi line chart that has the 24 months listed across the bottom and shows the count of backlog for each product per month (with each product as a series I guess). I guess my chart data source will have a c...

Stacked Bar Chart #2
I would like to create a stacked bar with categories of expenditures (personnel, capital outlay, operating expenses, etc.) with a stacked bar for each department in the budget AND I would like several stacked bars for each department with each bar representing a fiscal year. The x-axis would be departments. Does anyone know how to do this in Excel? If you stagger your data, you can create side-by-side stacked columns. For example, with two departments over two years: 2001 2002 Exp1 DeptA 100 120 Exp1 DeptB 50 60 Exp2 DeptA 90 ...

Pivot chart series label
Hello - I have a Pivot Chart where I am using the Data as the axis labels (example: several jobs where the data sums up the number of people in that category - 3 salesman, 2 managers, etc.) The issue is that the label showing in my chart says "Sum of Manager". Is there a way to get it to say only Manager? Thx! You can't use a label that's exactly the same as the field name, but this workaround works well: Right-click on the Sum of Manager button in the chart Click on Format PivotChart Field In the Name box, delete the existing text and type Manager, followed by a ...

Showing Significance Testing in Bubble Charts
We are preparing a bubble chart in which the x-axis shows scores from a survey, the y-axis shows relative importance, and the z-dimension shows the percentage point change in the score from the previous period. We have set the default color of the bubbles to grey (which means no significant change), and would like to make the significant changes colored in red (negative) or green (positive). We have already done the stat testing, and can mark the significance testing with a -1 (negatively significant), 0 (insignificant), and +1 (positively significant). We would like to be able to autom...

Printing a gantt chart
Hi, I have a gantt chart that runs,in days, for 9 months. How do I print it on multiple A3 pages?? I have tried page set up and chosen 'print to', and it prints only the element which is shown on the white grid page.. Hope this makes sense Kind regards Amanda The white grid area represents the printable area. You can use Page Setup to make it bigger or drag an edge of the white grid while holding the CTRL key to make the printable area bigger. John... Visio MVP Need stencils or ideas? http://visio.mvps.org/3rdparty.htm Need VBA examples? http://visio.mvps.org/VBA.htm V...

Export Chart as GIF on Macintosh OS X
I get an error message trying to export a chart on a Mac running OS X Excel version "X for Mac, Service Release 1" from the following code: Sub ExportChart() MsgBox (ActiveSheet.ChartObjects(1).Chart.Name) ActiveSheet.ChartObjects(1).Chart.Export FileName:="mychart.gif" FilterName:="GIF" End Sub ... I've never had issues on a PC...and the first MsgBox line work fine, just as a check... I get: "Application Error '1004'"... Thanks in advance, this is very frustrating... Ti -- Message posted from http://www.ExcelForum.com In article <t...

Spin Box Date and Sumproduct
Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000))). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containi...

Multiple Chart on Single Page
I'm having a lot of trouble arranging multiple charts on a page. Actually I'm doing 10 charts that are all the same size on 2 pages with 6 on the first page, then 4 on the last. I'm having a heck of time trying to get this right. I select all 10 charts and stretch them this way and that and adjust some row and column widths between the charts to make adjustments, but I can't get it right. There has to be a better method that this trial and error approach. I've search the web, but almost everything I've found is for some fancy charting program to do this. It...

On POS (not RMS) Adjusting Time Clock Defaults to Today's Date #2
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Really appreciate some help on merging two spreadsheets...
Hi All, I have two spreadsheets - one of them has a list of addresses and bandwidth requirements per site for a VPN. The other sheet has the same addresses in a different order with postcodes. I need to merge them together so I have one sheet which has both the bandwidth and the postcodes - is there any easy way to do this? Appreciate your help in advance. Rob. Yes: check out VLOOKUP in Help. It will look up an item in a range and return another cell from within that range. Note, however, that the spelling of site names or whatever your lookup value is must match exactly. Please po...

Comparing a date field with today's date
My report contains a date field 'Delivered_On' and a label 'lbl_Sited'; both have the visible format set to no. I am trying to get the date field to become visible if the date is greater than today's date, and the label to become visible if the date field is less than today. When I use the argument "If Me.Delivered_On >= Now() Then.....", I get an error "You entered an expression that has no value" -- Keith W ...

Rename Charts
I create a series of charts in a Sheet, every time Excel give a name to the chart. When seeing the chart window will look like [name of the workbook.xls]Chart 1, then I create a macro to print a set of Charts. The problem I have is on how to rename the chart name give by Excel, because when I create other chart on a new workbook excel assign a consecutive number for the new chart. Thanks Rene http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Rene wrote: > ...

need variable time axis available in XY chart for area chart #2
I am unable to use different X axis in the Area chart, which is available in the X Y charts. How do I use the X Y chart features of variable X axis to make the area chart? ...

Chart with colored weekends
Is it possible to have a date chart with a gray bar marking Saturdays, Sundays and declared Holydays so I can Gantt my deliveries? Just a few tips on how to get there... PS: by the way Jon, I've been learning A LOT from your examples. Thx for your excellent pages. ...

Formatting dates on x axis of chart...!
Win XP HE Excel 2002 Hi, I have a chart with a simple y and x axis. The x axis represents dates. In sheet1 of the spreadsheet being used, the dates are in column A and in each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the dates are in this format: 12/20/2004.) The result is (obviously) the same number and format 12/20/2004 in sheet1. When I chart, for the life of me I cannot get rid of this format in the x axis labels--I am usually able to define the label format in tha chart itself but, nothing I do lets me change this format. Even when unchecking the "link to sou...

Date conversion issue... again
Hello all, I have a file that contains dates. I know that for example "12.10.2004" means "December 10th, 2005", however Excel treats as "Oct. 12th 2004". Nothing works: converting the format of the cells doen't help, the TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial number instead Any suggestions? thanks a lot in some other empty cell (without any formt) type this =(SUBSTITUTE(A1,".","/"))+0 you may get the date number format it as you like do you get what you want;. Dimmer <Dimmer@disc...

problem with drawing vertical line on chart
I'm using Peltier's http://peltiertech.com/Excel/Charts/AddLineVertSeries.html to draw a vertical dotted line on a time series chart (to "split" the chart at the current date). But I can't get the vertical line in the right place. I think it's setup correctly: Today 5/21/2006 0 5/21/2006 1 I think the problem is the secondary axis scale. Excel thinks it's a number series instead of a date series. Any help on how to tell Excel it's also a date series, or any other ideas on how to get the line in the right place? Thanks. <barbetta3141@yahoo.com> ...

Evaluate Receipt Date in receipt
Is there a way to evaluate a receipt date inside of a CONDITION tag? I want to do <= and >= I've tried and it seems like I cant evaluate TransactionDate. (Yes, I am using >= and <=) Thanks.... ...

Date Calculation
I need to write a formula or VB code for the following: Say Column A contains dates and column B amounts. In column C, I need to calculate 10% of column B if the date in Column a is between 1 April and 30 September. If the date in column A is between 1 October and 31 March then calculate 15%. The problem is it need to work for any year and the date in column a will be in the format of dd/mm/yyyy. Your help will be much appreciated. Many Thanks in advance. Hi Into cell C2 enter the formula =IF(AND(MONTH(A2>3,MONTH(A2<10))),0.1*B2,0.15*B2) and copy it down -- (When sending e-ma...

Delete columns based on a cell reference date value
I have a sheet titled 'data' that has source information and row 1 has columns H to BG with dates. On sheet 'Control' is a cell that has a free format cell for dates to be entered. I have named this cell 'WCDATA' for ease of another piece of code. I am tring to create a macro that will locate the date specified in WCDATA on the data sheet and delete all columns from H until that which matches WCDATA. Fortunately I have been running on a test file and I have tried several options but after considerable hair pulling am seeking assistance as am getting...