+ Reply to Thread
Results 1 to 3 of 3

Dynamic Charts ... Data Problem

  1. #1
    Registered User
    Join Date
    05-10-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    4

    Dynamic Charts ... Data Problem

    I am using an article, "Dynamic Charting By Dates"

    http://pubs.logicalexpressions.com/P...cle.asp?ID=246

    to create well, dynamic charts ... everything works the way it is supposed to so far but not exactly as I need it to ... I am hoping someone can help me change it slightly ...

    I am up to the point of creating the 'Magic Formula' to populate the parallel data (which I have done) and everything works so far as it has been described ... but what it does is take a date for EVERY possible day in between the Start Date and End Date and that works if there is data for EVERY day ... my data isn't every day, at most it would be 5 days out of the week. On the days that there isn't data, it adds the data from what I think is the last date that there was data. So, if I go 3 days without entering data I have 4 entries with the exact same data (the one I entered data for and the 3 that I had no data for that it copied it) ... now, I haven't built the actual charts yet and I may not completely understand the logic involved but if I do a daily chart, I believe, it will give me identical entries for each of those 4 days ... if I understand it, I don't think that is going to work for this application.

    My question is this: how do I get the parallel data 'Date' field (and subsequently the data attached to that date) to populate only if there is a corresponding date in the actual data?

    If that make sense?

    So, the formula in the first 'Date' field in the parallel data is the actual start date that I am beginning at and then the formula to gather the second date is: =IF(NewDate>EndDate,NA(),NewDate)

    where NewDate is a named formula that equals: =DATE(YEAR(ActivityGR!$S20)+ActivityGR!YearIncr,MONTH(ActivityGR!$S20)+ActivityGR!MonthIncr,DAY(ActivityGR!$S20)+ActivityGR!DayIncr)

    then the parallel data is calculated by the following formula:

    =IF($S20>EndDate,NA(),VLOOKUP($S20,AllData,COLUMN()+1-COLUMN($S20)))
    where: S20 is the date associated to that data, AllData is a named reference for all of the data and COLUMN() is something I am not sure what it references, probably the entire column ...

    thank you for any help ...

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,929

    Re: Dynamic Charts ... Data Problem

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Dynamic Charts ... Data Problem

    Column() returns a number. In this case, it will be the number of the column where the formula is. It is used to determine which column from the Lookup_table the Vlookup formula shall return. Try to hard-code a number in there instead of Column(). Then, once you've found the value you need, return the Column() function and adjust the offset.

    COLUMN()+1-COLUMN($S20) will return the number of the current column, plus 1, and then subtract 19 (column S is the 19th column).

    Unless you understand that concept, you won't be able to tweak the formula to your needs.

    cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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