+ Reply to Thread
Results 1 to 4 of 4

Formula to change chart data source for all dates up to today

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Formula to change chart data source for all dates up to today

    Hi,

    I have a spreadsheet that I input data into daily. I then use an if and vlookup on the date to pull through any data earlier than todays date and this is charted. Unfortunately though my chart shows all the sata, including the dates with nothing in, so the line drops down to nothing and doesn't look good.

    Is there a formula I can put into the data source box so that it only displays the data from today and earlier?

    Graph.jpg

  2. #2
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: Formula to change chart data source for all dates up to today

    jhalsall80,

    You can filter your source data, which will cause your graphs to adjust in accordance to the filters you've added.

    "Data" tab > "Filter" > then enter a date filter with the criteria you need.

    One thing you will need to change is your data structure. List it vertically instead of horizontally. Or just hide those columns.

    If this doesn't help attach an example so we can better help you. Thanks.
    Last edited by BrownBoy; 05-09-2014 at 12:05 PM.
    BrownBoy

    If happy, mark "SOVLED" & add to "REP"

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Formula to change chart data source for all dates up to today

    Hi BrownBoy,

    I am trying to list it vertically but don't want to retype in all my formulas so wondered if you could help. My formula currently reads =IF('2014 Data'!A$1<=$AK$1,VLOOKUP($B$1,'2014 Data'!A:E,2,0),""). If I drag the formula down it changes the non-absoluts to B:F where I actually want it to jump five cells right to F:J. Is there any way I can get it to do this?

    Thanks

  4. #4
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: Formula to change chart data source for all dates up to today

    You can always copy the data and then transpose it:

    under the "Home" tab > "Paste Special" > check the "Transpose" box > "OK"

    Judging by how your data looks the formulas should adjust. If you do this, you will want more meaningful column headers and in addition you will need to adjust where each chart's data is referenced from.

    If possible, please attach a dummy file with false data for each of your sheets, but with the same formula(s) and structure. This will allow us to see exactly what you are doing and we can better help you that way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to automatically change source data in a chart?
    By els_pricing_heather in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-29-2011, 10:29 AM
  2. Excel 2007 : Copy chart, change data source
    By Mimil in forum Excel General
    Replies: 5
    Last Post: 02-03-2011, 08:08 AM
  3. Change Data Source of Pivot Chart
    By riluma in forum Excel General
    Replies: 1
    Last Post: 07-16-2010, 01:25 PM
  4. Excel 2007 : Change chart source data in XL2007
    By normc in forum Excel General
    Replies: 2
    Last Post: 12-31-2009, 05:07 PM
  5. change Line Chart source data in VB
    By Centurian77 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-20-2005, 12:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1