Graphing dynamically in Excel

[westma]

Greetings, Here is my situation.
I have two columns with data, column A has timestamps with the date and time at ten-minute intervals, and column B has the data corresponding to the timestamps. The data can run over any length of time.
I am trying to create a line graph of this data, but I want to graph data taken only during a user-desired month. Thus, in another cell, the user enters a 1-12 depending on the desired month, and the graph shows data which falls in that month.
I thought I could accomplish this without a macro. In two other cells, say P29, P30, there are formulas which give the cells containing the data corresponding to the first and last timestamps of the month, and they change depending on the month entered by the user.
I insterted a Name in the workbook and referred it to the following formula: =INDIRECT(P29):INDIRECT(P30)
and inserted this Name into the SERIES formula for the graph. This didn't work!
I then created formulas in cells Q29, Q30 which give the row numbers of the start and stop times for each month. For the Name, I referred it to the following formula: =OFFSET(INDIRECT(DataSheet!P29),0,0,(DataSheet!Q30-DataSheet!Q29)+1)
and inserted into the SERIES formula for the graph. This didn't work either, and both times I received and error stating my formula contains an invalid external reference to a worksheet. I believe that my problem lies within having the INDIRECT funcion in the formula, but I don't know of any other way to make the data range change according to the month. Does anyone have any ideas on what I could do? Thank you very much for your help!

 

[000Steve000]

I just put the formula =INDIRECT(B1) in Cell A1 and the formula =INDIRECT(B2) in cell A2. I typed "C1" in B1 and "C2" in B2. I put numbers, representing data in C1 and C2. I then was able to plot the data (from C1&C2) using =Sheet1!$A$1:$A$2 as the series data. So I guess what I am trying to say is if you put the indirect function into another row and then plot the new row your initial thought might work.

 

[westma]

Thanks for your input. This works when you only have two numbers to plot, but not so well for me because I have to plot a couple thousand at a time. It wont work when I have to plot from cell C1, shown in cell B1, to cell C2594, shown in cell B2, becuase the indirect functions in a will give me two numbers, and only those two numbers will be plotted. Any other ideas? Thank you!

 

[westma]

Is there any way to do this that bypasses having to use the INDIRECT function?

 

[byundt]

You can build your plot using dynamic named ranges whose extents are set by cells P2930.

Let's suppose that your data starts in cell A2 and continues without any blanks in columns A and B. Cell P29 contains the value that represents the starting point in column A. Cell P30 contains a value that represents the ending point in column A. The chart will then plot all the data that occurs between P29 and P30.

The dynamic named range XData may be created with the Insert...Name...Define menu item. Enter XData in the "Names in workbook" field at the top. In the "Refers to" field at the bottom, enter the following formula:
=OFFSET($A$2,MATCH($P$29,$A$2:$A$65536,0)-1,0,MATCH($P$30,$A$2:$A$65536,0)-MATCH($P$29,$A$2:$A$65536,0)+1,1)

Repeat the procedure for YData using the "Refers to" formula:
=OFFSET($A$2,MATCH($P$29,$A$2:$A$65536,0)-1,1,MATCH($P$30,$A$2:$A$65536,0)-MATCH($P$29,$A$2:$A$65536,0)+1,1)

Next, right-click one of the points on your existing chart. Select "Source data" from the pop-up, then choose the Series tab. In the field for X-Data, replace the cell addresses after Sheet1! with XData. Do the same with the Y-Data, replacing the cell addresses with YData.

You now have a dynamic chart. If you change the starting or ending date/time serial number in cells P29 or P30, the chart will select different data and update itself.

If you want to have the user choose a month & day, then just have a formula in cells P29 and P30 that calculates the exact serial numbers that you want the plot to start and end.