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