3

In my Excel workbook (i.e., file), I have for example seven sheets (Mon, Tue ... Sun) of temperatures.  This data is recorded for the whole day in a fixed frequency.

Now I want to create a tool to view this time series data easily.

My thinking is, I create a page, say, Index, which has the list of names of all these sheets, there is a chart object, which displays the data from sheet Mon by default, and a button/or a drop down list for me to change Mon to any name (which connect to the sheet), and refresh the chart to display the corresponding data.

How should I approach this? I am just a very basic Excel user, but I am willing to learn the skill.

1

Since you haven’t given us detailed information on how your sheets are laid out, I’ll assume that you have times in cells A2:A25 and temperatures in cells B2:B25 on each of your daily sheets, like this:

Input data for Monday, Tuesday and Wednesday

On your Index sheet, enter

  • In A1, “Day”.  (This is just a label; the same as you have shown in your image of your desired result).
  • In B1, your drop-down list of the days of the week.
  • In J1, “Time”.  (This is just a label, and is optional.)
  • In K1, “Temp”.  (This is just a label, and is optional.)
  • In L1, =B1 & " Temp".  This will be the chart title.
  • In J2:J25, the times 00:00 through 23:00.  Or, if some sheets have different “X”-axis values (for example, if some days have 00:30 through 23:30), enter
        =IF(INDIRECT($B$1&"!A"&ROW())<>"", INDIRECT($B$1&"!A"&ROW()), "")
        
    in J2 and drag/fill down to J25 (assuming that you always have 24 data points).  You may need to manually format this correctly (e.g., hh:mm).  This will access time data (i.e., X-axis labels) from Column A of the indexed daily sheet, forcing empty cells to be treated as blank rather than zero (see Display Blank when Referencing Blank Cell in Excel).
  • In K2, =IF(INDIRECT($B$1&"!B"&ROW())<>"", INDIRECT($B$1&"!B"&ROW()), #N/A).  This will access temperature data from Column B of one of the daily sheets, replacing empty cells with #N/A (the “not available” pseudo-value), which will cause the corresponding data points not to be included in the chart.  Select K2 and drag/fill down to K25.  The temperature data for the selected day will appear.

Now create your chart based on J1:K25 of the Index sheet.  Click on the chart title, and type =Index!L1 into the formula bar.  Then simply select the day that you want in Index!B1, and the chart for that day’s data will automatically, immediately appear — no need to press a button.

Chart for Monday

Chart for Tuesday

Chart for Wednesday

Sample result with missing data:

Chart for Monday with missing data

Of course the choice of cells is arbitrary.  I used Columns J through L so I could fit them on the page with the chart.  If you’d rather not see the selected data, use Columns AA through AC so they’ll be out of sight — and/or hide the auxiliary columns.

5
  • Scott, why did you delete the sample image from OP? – Alex M Mar 9 '19 at 18:41
  • 1
    Because I believed that it did nothing to clarify or illuminate the question.  All it shows (and not very clearly) is a choice among the days of the week, and a chart that (presumably) is based on data from the Monday page.  But the question says all that.  In my opinion, this picture is worth zero words. – Scott Mar 9 '19 at 18:46
  • Thank you very much! I didn't expect it can be this simple, just one key function in here, indirect. Many many thanks – Victor Mar 9 '19 at 22:01
  • May I also ask another question? in fact some days I have less data, for example, Mon until 22:00, Tue until 23:00, so data range is not fixed. If I use the max range, that means a short range will have zero value at the end, how can the chart ignore these zero value dynamically please? – Victor Mar 9 '19 at 22:30
  • I expanded my answer to cover your follow-up question. – Scott Mar 13 '19 at 23:04
0

This is a vba macro, updating the chart-data. Without error-checking and most names for a better understanding

Private Sub SetChart(sheetChrt as worksheet, sheetData as worksheet)
 With sheetChrt.ChartObjects(1).Chart.SeriesCollection(1)
   .XValues = sheetData.Range("A1:A10") '
   .Values = sheetData.Range("B1:B10")
 End With
End Sub

Public Sub Test1()
 with Thisworkbook
 SetChart .Sheets("MyChartSheet"), .Sheets("MyDataSheet")
 end with
End Sub
1
  • Thanks, this is worth to learn. – Victor Mar 9 '19 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.