Forum Discussion
Using Combo Box to change data on page
You can specify a month using data validation via a dropdown list, which is simpler to implement and less likely to allow invalid input than a combo box.
For an example, see the attached workbook. Worksheet KPHX contains daily temperature and precipitation data recorded at Phoenix, Arizona. (I've included only the data since December 2022, as that's what I have at hand.) On worksheet Control Data, a set of formulas builds the text to show in the dropdown list on cell B2 of worksheet Subset. I used just part of the generated list to set up data validation for cell B2 via menu items Data → Data Tools → Data Validation → Data Validation…On worksheet Subset, formulas in cells B3 and C3 calculate the start date(-time) and end date(-time) for formulas on that sheet, respectively. (These values do not have to be visible.) You did not indicate if your historical data is recorded with dates only, or if it is recorded with dates and times. I included 11:59:59 PM in the calculation of the end of the month (cell C3), and that will handle either case.
(BTW, this can be also be accomplished using two dropdown lists — one for the month, and one for the year — typically with two corresponding data validation lists.)
Your description of "have [the sheet] automatically populate the rest of the cells" is understandably vague. Formulas in cells C4:C7 on the Subset sheet demonstrate how the built-in Excel functions COUNTIFS AVERAGEIFS, and SUMIFS summarize data from KPHX, as each row used by those functions includes a date value within the selected month.
To show non-summarized data, the built-in function FILTER can be helpful. I used it in a formula in cell B10. This formula retrieves all six columns of data (it could retrieve fewer) for all rows where the observation date falls within the month. (Note that there are two filtering criteria, connected with a multiplication operator because both must be true.) This works whether or not the data is sorted by date(-time). Unfortunately, the FILTER function converts empty cells in the retrieved data into cells containing zeros; additional work is needed to suppress inappropriate zeros. I have used custom formats in columns E and F to hide zeros there.
But if your data is structured such that you need to retrieve data from other rows that do not include a date(-time) value, you might need to use a combination of the built-in functions MATCH (to locate a specific date(-time) value) and OFFSET (to retrieve a range of cells relative to the MATCHed cell).
That example should get you started. Feel free to ask follow-up questions.
You can specify a month using data validation via a dropdown list, which is simpler to implement and less likely to allow invalid input than a combo box.
For an example, see the attached workbook. Worksheet KPHX contains daily temperature and precipitation data recorded at Phoenix, Arizona. (I've included only the data since December 2022, as that's what I have at hand.) On worksheet Control Data, a set of formulas builds the text to show in the dropdown list on cell B2 of worksheet Subset. I used just part of the generated list to set up data validation for cell B2 via menu items Data → Data Tools → Data Validation → Data Validation…
On worksheet Subset, formulas in cells B3 and C3 calculate the start date(-time) and end date(-time) for formulas on that sheet, respectively. (These values do not have to be visible.) You did not indicate if your historical data is recorded with dates only, or if it is recorded with dates and times. I included 11:59:59 PM in the calculation of the end of the month (cell C3), and that will handle either case.
(BTW, this can be also be accomplished using two dropdown lists — one for the month, and one for the year — typically with two corresponding data validation lists.)
Your description of "have [the sheet] automatically populate the rest of the cells" is understandably vague. Formulas in cells C4:C7 on the Subset sheet demonstrate how the built-in Excel functions COUNTIFS AVERAGEIFS, and SUMIFS summarize data from KPHX, as each row used by those functions includes a date value within the selected month.
To show non-summarized data, the built-in function FILTER can be helpful. I used it in a formula in cell B10. This formula retrieves all six columns of data (it could retrieve fewer) for all rows where the observation date falls within the month. (Note that there are two filtering criteria, connected with a multiplication operator because both must be true.) This works whether or not the data is sorted by date(-time). Unfortunately, the FILTER function converts empty cells in the retrieved data into cells containing zeros; additional work is needed to suppress inappropriate zeros. I have used custom formats in columns E and F to hide zeros there.
But if your data is structured such that you need to retrieve data from other rows that do not include a date(-time) value, you might need to use a combination of the built-in functions MATCH (to locate a specific date(-time) value) and OFFSET (to retrieve a range of cells relative to the MATCHed cell).
That example should get you started. Feel free to ask follow-up questions.