Charting Non-Contiguous Data Ranges

Hi all. I'm new poster but experienced excel user. I've never messed with the VB angle, nor macro's, but have lots of experience working with the functions and formulas that are provided with excel. That being said, you can expect that this question is probably not going to be easy. (but I could be wrong.)

What I have is an excel workbook to log and analyze my daily readings from my diabetes meter. The 'Data Input' sheet has a row for each day, and each row consists of, (among other things not inportant to this question), five 2 column sections for my readings. They are Breakfast, Lunch, Dinner, Bed Time, and Special (the last one is usually blank. But is there for readings that don't fit the first four)

Each section has two columns, Time (time of the reading) and Reading.

Now.. I have a second sheet, called Trends, where i'm using frequency to count reading ranges. These use Offsets, as new days are inserted into top of the 'Data Input' section (by copy and insert copied), and the frequency is reading only the top 7 or 30 lines. (giving me a rolling weekly and monthly figgures)

I had to use five Offsets each with a single column width, because excel treats the times as numbers which was throwing off my frequencies.

What I would like to do, is create a line chart, that shows all the readings (skipping blanks) for the previous week and/or month, using offsets that check the 5 sections, for previous 7 and/or 30 days.

I have no idea how i'm going to accomplish this. Any help?

I am using Excel 2007, and have experience with programming (C++) so can handle the more technical angle of formulas and functions. A copy of the excel is included. any other suggestions would be welcomed.

It's not clear to me exactly what you want to chart.

What I can tell you is that the simplest approach would be to have code that copies the data in to a contiguous range that you can then create a chart on.

Can you manually create an example of the data you are trying to plot and then we can think about coding it.

hrm.. yes, I'm afraid i can be a bit ambiguous at times.

Basically, What I need to do is chart every other cell per row, for 7 and/or 30 rows.

The data would be in cells D, F, H, J, and L starting at most recent on row 10.

So what I'd like to do is have a line chart, showing how the readings go up and down from reading to reading, skipping blank cells.

I really like having the time mixed in on the data input sheet, as it makes it easier for me to read and keep track of when each reading occured, however, perhaps i need to find a different way to layout the data. But I can't come up with something both functional and asthetic.

As far as re-creating the data.. the only way that comes to mind is to have an offset(..) for each individual cell. 7 times 5 is 35 forumlas. we won't go into doing 30 days using that method.

Add to that, that doing that means the blank cells arn't blank and that would interfere with the chart itself. (I ran into that with the chart I did from the trends. Had to work around it using NA() and conditional formatting.)

Is the attached something like the chart you where expecting?

yea.. very much what i was looking for, (although reversed) but in order to get that chart, i guess I will need to re-organize how the data is entered.. one reading per row instead of 5. Sort of how you did.

This is something I am considering. However it leads to another problem I would have to figgure out.

If i use date in column A, time in column B, and reading in column C, I need to feed the frequency data (and even that chart) a dynamic range that would include only readings who's dates are current date minus 7 days, or current date minus 30 days.