# Creating a dynamic chart range where the range to be graphed depends on the contents of a cell

N

#### NY

Background
-----------------

I have a spreadsheet with many rows of data, each with a date and time and
the values of various numerical parameters read at these times:

01/01/14 00:00 1.1 2.2
01/01/14 00:10 1.1 2.2
01/01/14 00:20 1.1 2.2
01/01/14 00:30 1.1 2.2
01/01/14 00:40 1.1 2.2
01/01/14 00:50 1.1 2.2
01/01/14 01:00 1.1 2.2
01/01/14 01:10 1.1 2.2
etc
02/01/14 00:00 1.1 2.2
02/01/14 00:10 1.1 2.2

Hopefully the number of readings per day is the same every day - in this
case, 6 readings/hour * 24 hours = 144 readings - though I suppose I ought
to cope with the case where a reading is lost.

I want to plot a graph of one of the values (eg the third column) versus
time for a chosen date, with the date (or at least a simple integer
representing the day number) entered into a spreadsheet cell.

For one specific hard-coded day I can create an XY scatter graph with
formulae

X='Jan14log'!\$B\$2:\$B\$145
Y='Jan14log'!\$C\$2:\$C\$145

In general, the start and end rows for any day number are:

start_row=Offset+DayNumber*RowsPerDay
end_row=Offset+(DayNumber+1)*RowsPerDay-1

where Offset is 2 (to account for the first row being column headings) and
RowsPerDay is 144 (6*24) - the simplest case where there are always the
same number of readings per day.

Question
-------------

So my question is: how do I use these calculated start/end row values in the
formula for the chart?

I want the formula to be of the form

Y='Jan14log'!\$C\$start_row:\$C\$end_row

so that when I change the DayNumber cell, I get a graph for a different
day's data.

Refinement
----------------

It would be nice to be able to devise a formula which says "extract from all
the data those rows whose column A matches a specified date and plot an XY
graph of X=column B and Y=column C for those rows", so as to cope with the
case where there are different numbers of rows for different days because of
missing data.