Dynamic source data based on Today

I am creating a production barchart that is updated daily.  I want the chart 
to plot from 3 days old to 4 days in the future.  I would like the middle of 
the chart to represent today.  Tomorrow's data will become today's data when 
everything shifts by one at midnight.  

I am pretty new to macro so please speak in laymans terms.
0
SPISO (2)
4/21/2009 3:35:01 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
471 Views

Similar Articles

[PageSpeed] 47

No need for a macro.  Adapt the ideas at
Dynamic Charts 
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly example 2
-- 
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select 
from the menu


"SPISO" wrote:

> I am creating a production barchart that is updated daily.  I want the chart 
> to plot from 3 days old to 4 days in the future.  I would like the middle of 
> the chart to represent today.  Tomorrow's data will become today's data when 
> everything shifts by one at midnight.  
> 
> I am pretty new to macro so please speak in laymans terms.
0
4/21/2009 7:20:01 AM
What if there is not any set number of future days in my data table.  For 
example, some times we have 4 days in the future and other times we have 
scheduled 10 days in the future.  

I still only want the chart to show Today in the middle, 4 days in the 
future, and 3 days in the past in the chart.

"Tushar Mehta" wrote:

> No need for a macro.  Adapt the ideas at
> Dynamic Charts 
> http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
> particularly example 2
> -- 
> Tushar Mehta
> http://www.tushar-mehta.com
> Custom business solutions leveraging a multi-disciplinary approach
> In Excel 2007 double-click to format may not work; right click and select 
> from the menu
> 
> 
> "SPISO" wrote:
> 
> > I am creating a production barchart that is updated daily.  I want the chart 
> > to plot from 3 days old to 4 days in the future.  I would like the middle of 
> > the chart to represent today.  Tomorrow's data will become today's data when 
> > everything shifts by one at midnight.  
> > 
> > I am pretty new to macro so please speak in laymans terms.
0
SPISO (2)
4/21/2009 1:07:02 PM
Suppose you have the data in cols. A and B with row 1 as the header.  Further 
suppose you have the 'center date' and the number of past days and number of 
future days to show in individual cells named as below.

All the names are sheet-level names.

CenterDate	=Sheet1!$D$2
NbrFutureDays	=Sheet1!$D$4
NbrPastDays	=Sheet1!$D$3

Now, create the 3 named formulas below and plot XVals and YVals as the 
x-values and y-values of the series.

AllXVals	=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
XVals	=OFFSET(Sheet1!$A$2,MATCH(Sheet1!CenterDate,Sheet1!AllXVals,0)-1-Sheet1!NbrPastDays,0,Sheet1!NbrPastDays+Sheet1!NbrFutureDays+1,1)
YVals	=OFFSET(Sheet1!XVals,0,1)

-- 
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select 
from the menu


"SPISO" wrote:

> What if there is not any set number of future days in my data table.  For 
> example, some times we have 4 days in the future and other times we have 
> scheduled 10 days in the future.  
> 
> I still only want the chart to show Today in the middle, 4 days in the 
> future, and 3 days in the past in the chart.
> 
> "Tushar Mehta" wrote:
> 
> > No need for a macro.  Adapt the ideas at
> > Dynamic Charts 
> > http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
> > particularly example 2
> > -- 
> > Tushar Mehta
> > http://www.tushar-mehta.com
> > Custom business solutions leveraging a multi-disciplinary approach
> > In Excel 2007 double-click to format may not work; right click and select 
> > from the menu
> > 
> > 
> > "SPISO" wrote:
> > 
> > > I am creating a production barchart that is updated daily.  I want the chart 
> > > to plot from 3 days old to 4 days in the future.  I would like the middle of 
> > > the chart to represent today.  Tomorrow's data will become today's data when 
> > > everything shifts by one at midnight.  
> > > 
> > > I am pretty new to macro so please speak in laymans terms.
0
4/21/2009 3:33:01 PM
Reply:

Similar Artilces:

How can I get data from one worksheet into another exsiting worksh
Here is my case: I already have 2 worksheets both include 2 columns: Cell & Site Name. WorkSheet A includes all the cells and site names; WorkSheet B includes only a small portion of Cells. Now what I need to do is to get the site name for each cell in worksheet B. I don't want to manually copy & paste. Is there any way to speed up? Thanx! Cell Site Name T50MB7 T73MA7 T73MA8 T73MA9 T04MV9 T04MW7 T34MK7 T34MK8 T51ME1 T51ME2 T51ME3 T04MV1 T34OR2 T34OR7 T34OR8 Sabrina, you can use the VLOOKUP formula to fill in your Site Names in Worksheet B. Example: =VL...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

How to clear corrupt data on Outlook calendar?
I have bought a new Blackberry but am unable to backup the calendar - I get a "corrupt data" message for an old item, and I cant find a way to get rid of the item. Any ideas please? Geoff What type of item is being reported? Is it a reminder? Try starting Outlook (next time report your version please) with the /cleanreminders switch. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, swallows...

Printing Autofilter Data
How do I print Autofilter data onto one sheet instead of a few lines on say 20 pages? In page Setup, select fit to print and select 1 page wide and 1 page high. -- Regards, Tom Ogilvy "Confused" <Confused@discussions.microsoft.com> wrote in message news:2C7DCB67-06B2-429C-99D9-D2B3A40FEF4A@microsoft.com... > How do I print Autofilter data onto one sheet instead of a few lines on say > 20 pages? Thank you! "Tom Ogilvy" wrote: > In page Setup, select fit to print and select 1 page wide and 1 page high. > > -- > Regards, > Tom Ogilvy >...

Referencing using data in a cell? #2
thanks, that was perfec -- altitude2 ----------------------------------------------------------------------- altitude2k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1506 View this thread: http://www.excelforum.com/showthread.php?threadid=26685 ...

Trying to "Grey Out" unwanted fields in a data entry form
I have a table with about 20 fields, one of them being "Analysis Type". There are 6 of these types. I then have a form in my self- made switchborad with 6 command buttons, all allowing the user to choose which type of analysis he wants - which then leads to the appropriate form. Now, what I am trying to do is make one big generic data entry form for each analysis type. For instance, if the user selects analysis type "X", then the generic data entry form comes up with all fields in the form listed, but the ones not pertinant to that analysis type are "greyed out&quo...

Upgrading Dynamics 9 to 10
All, I am planning a Dynamics upgrade for a client that currently uses version 9.0 and we are going to upgrade them to 10.0. I have read numerous posts about upgrade paths and documentation but have not been able to locate the upgrade papers that they are talking about. Does anyone know where I could get my hands on white papers or documentation on steps that i have to take to upgrade to 10.0? Thank you, Keith Keith, see below link for upgrade instructions to Microsfot 10 https://mbs.microsoft.com/customersource/documentation/setupguides/gp10_upgradeguides.htm?printpage=false#System...

Move emails automatically from Inbox to other folder, based on dat
Some of my users have thousands of emails in their Inbox which slow down performance. Is there a tool with which I can automatically move items older than 30 days to a different folder in Outlook? Based on message class would also be fine, since I use Vault mail archiving, which changes to message class from IPM.Note to IPM.Note.EnterpriseVault.Shortcut. Thanks! Chiel Varkevisser The Netherlands In news:F458372B-155A-4A83-A1F3-4E860477A3D4@microsoft.com, ChielV <ChielV@discussions.microsoft.com> typed: > Some of my users have thousands of emails in their Inbox which slow > do...

Contouring data from rows and columns
I have created several contour (surface) plots, but cannot enlarge the size of any of these graphs. I would appreciate any suggestions or help. Thanks, Mangat Mangat - Sometimes when you adjust the 3D view of a chart like this, Excel shrinks it. What it does, really, is shrink the plot area. If you click right near the walls of the chart, not on an axis but well within the outline of the chart object, you will activate the plot area, and it will be surrounded by a thick gray rectangle. You can resize this rectangle. Sometimes it isn't possible to make it exactly the way you w...

lead source field not populating during import
I added more choises to the lead source pull down. Restarted IIS. When I go into CRM they show up fine and I can choose them for a new lead. However I am importing leads (using Scribe) and I cannot get this field to populate. If I assign the number value associated with each pull down choice I get the number in that field. If I put nothing in that field to import when I go into CRM only the original choices for the pull downs are there, not the ones I added. I know I published them because I can see them if I'm just adding a lead from within CRM Any suggestions anyone. Hi...

Project-based Purchase Receipt Return in GP 9
A user entered a purchase receipt for a non-inventory item against a project and posted it. The receipt has not been billed and never will be so that's not an issue. If the receipt has not been invoiced, should she simply enter a Return transaction? If the receipt has been invoiced but we just want to get rid of the cost from the project, what is the best way to handle this? -- Charles Allen, MVP ...

Data Consolidation #2
Hey All, ...

Directly link item engineering data rev level to PO entry/update
Common industry standard is to note the revision level of the item ordered on the PO... this is required for ISO. There is no direct table link from item engineering data to the PO Entry/update regarding item's current revision level. -- Steve Laurenzano K-Rain Manufacturing ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based News...

vertical centering of data
I had to create a text box that is a little larger vertically than a normal text box. When the user enters data in the text box, it appears at the top part of the text box leaving a little more space at the bottom of the text box than what is visually appealing. It would be nice to center the data inside the text box so that there is equal space above and below the data entered in the text box. Is there any way to center the data inside the text box so that there is equal spacing both vertically as well as horizontally? I know this is getting a little picky but as I said, i...

Automatically pull data from one file to populate a template
Hi Everyone... Just beginning to get the hang of vba coding in excel. I would like to implement a new feature in a spreadsheet here at the office. We currently have a master records sheet containing vital information on unit processing. Each unit then has a sub file containing additional information. Currently one must populate the master record file. Then open a template and populate another file with the same information. I would like to streamline this process and have the data in the master record file automatically inserted into the template, allowing the user to only have to ...

Need help in data copying. #2
Hi Dave Thanks for your reply. It is working perfect *if there is more than one row*. But If I have the data only one row (B8 in my case) , then it selec the data till the end of the file ( till Row no. 65536) I will appreciate your reply. Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=25956 SMILE, Don't use .End(xlDown) For example, if the code is Range(ActiveCell, ActiveCell.End(xlDown)...

How do I sum YTD totals based on monthly totals
I have a financial worksheet with expenses by month and a monthly total for each month. At the end of each subsequent month there is a cumulative total of the monthly totals (YTD), I cannot figure out how to get an automatic formula to populate the correct cells to mimick the previous formulas with the current cell data to get the correct totals. -- Donna EMU Alumni "Bsgrad02" <Bsgrad02@discussions.microsoft.com> wrote in message news:8E3201DF-9B59-4188-8E19-BCC24B00012C@microsoft.com... > I have a financial worksheet with expenses by month and a monthly total for &g...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

Mail Merge (Directory) using Outlook Contacts as source
I am very comfortable in doing a mail merge using various sources, but have never used Outlook Contacts. When I choose "Select from Outlook Contacts" and then "Choose Contact Folder" I can only access the contact folder in my personal folders rather than the main contact folder in Outlook. Any thoughts? Frank Start in the contacts folder you want to use and choose Tools | Mail = Merge.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart...

Merge cell & Copy Data from different cell
Can data been copy from a merge cell, if possible pls explain. How to copy word from another cell. Thank you ...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

How to change pivot table source database?
How can I change the source database for a pivot table in an Excel spreadsheet. Our database server has been changed and when I create a new dsn pointing to the correct server, I get an error message indicating that the query on the old server is still trying to be used. Thanks, Steve This should work for you (?) Open the pivot table and click on it. Menu - Data/Pivot Table report. Click the Back button on the Wizard dialog that appears and connect t the new data source -- Message posted from http://www.ExcelForum.com ...

Mapping Geographic Data
I want to create a data map and I need to have Microsoft Map installed. Apparently Microsoft Map was not installed when our IT Department installed Excel (I am running Excel 2003). I believe I ahve to rerun Setup to install Microsoft Map however my IT Department says they can't find Microsoft Map in the Setup file. Can any one help me tell them how to locate Microsoft Map in the Setup file? Hi, Map was dropped as a free add on to excel after xl2000, I think that's the version. Anyhow to get Maps in xl2003 you will need to buy the standard alone program. Cheers Andy Mike wro...

Printing memo data from a form
I have a form which contains a memo field which may require more than one page to print. I can only get my printer to print out what is almost a screen shot of the page. How can I set up a command button which will print out the form with all the data from the memo field? Martin, You will run across that problem when trying to print forms. Forms are desinged to be used like menus (inputting data), reports are designed for printing. Create a report and set the field to Can Grow = Yes. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremo...

How to Plot This Data?
I am working in Excel 2007 and am having a hard time ploting this data. I have the following titles with associated dollar amounts and need to have the Titles show up on the X axis with the data showing vertically above each. I tried a scatter diagram but then it plots each dollar amount as a separate series. What I want is Title 1 and then $15 and $12 plotted vertically with same symbol like it would show up horizontally on a line chart. Title 1 $15.00 $12.00 Title 2 $5.00 $6.00 Title 3 $8.00 $4.00 Title 4 $2.00 $6.00 -- Catrina Make a Line chart; se...