Forum Discussion
Using Combo Box to change data on page
I have a spreadsheet on which I have a long series of historical data that is updated monthly going back through 2010. I am requested by my team to send out a page each month that has just the most up-to-date numbers displayed in a more condensed and simplified page (and occasionally may have use for doing this for previous months).
I have this simplified page set up exactly as I need it - however, as it is right now, I have to manually fill each of these cells every month by going back and referencing the larger spreadsheet. I am hoping to set up a way to get up the sheet so that by using a combo box or similar mechanism, I can tell the sheet which month and year to reference and have it automatically populate the rest of the cells. based on the matching row in the spreadsheet. Is this possible, and if so how should I go about this project?
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.
2 Replies
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.it's certainly possible to set up a mechanism using a combo box to select a specific month and year, and then have the rest of the cells automatically populated based on the selected values. My knowledge about combo boxes (active or not) is limited, but since no one has answered this, here is a general guide on how to approach this topic:
1. Create a Combo Box:
- Insert a combo box from the Developer tab. If you don't have the Developer tab visible, you can enable it in Excel options.
- Right-click on the combo box and go to "Format Control." Set the input range to the list of months and years you want to display in the combo box.
2. Write a VBA Macro:
- Go to the Developer tab and click on "Visual Basic" to open the VBA editor.
- In the editor, you can write a VBA macro that gets triggered when the combo box value changes.
- Here's a simple example of what the VBA code might look like:
Vba Code is untested, please backup your file before use.
Private Sub ComboBox1_Change() Dim selectedMonth As String Dim selectedYear As Integer ' Assuming your combo box is named ComboBox1 selectedMonth = Left(ComboBox1.Value, Len(ComboBox1.Value) - 5) selectedYear = Right(ComboBox1.Value, 4) ' Call a function to populate the rest of the cells based on the selected values PopulateData selectedMonth, selectedYear End Sub Sub PopulateData(month As String, year As Integer) ' Your logic to populate the cells based on the selected month and year ' Access the larger spreadsheet and extract the relevant data End Sub3. Populate Data Function:
- Implement the PopulateData function to fetch data from your larger spreadsheet based on the selected month and year. You might use VLOOKUP, INDEX/MATCH, or any other suitable method depending on your data structure.
4. Link Combo Box to Cell:
- Link the combo box to a cell where you can store the selected value. You can use this cell in your VBA code to determine the selected month and year.
This is a high-level overview, and you may need to adapt the code and logic based on your specific requirements and data structure. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Formularbeginn
