Dynamic Charts ... Data Problem

**RegLook** · 05-11-2010, 01:31 PM

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 ...

**davesexcel** · 05-12-2010, 09:10 PM

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!

**teylyn** · 05-13-2010, 12:06 AM

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