Excel Macro and Chart Generation Help

Question

I am looking to create a page in my book that can provide a visual representation of scores by personal, over a 4 week average. The idea is to have two drop down option conditions in which bar graphs will be formulated. (I.E Supervisor/All and by date) Based on the date and person selected I want to be able to have excel generate 4 separate bar graphs ( as there are 4 different tests done daily) displaying the past 4 week averages.

Visual representation of what I am looking for

Attached is a photo of the input page

Input Page

I know basic excel. But determining how to create this I am stumped.

itsberlinas · Answer

To create a dynamic page in Excel that provides a visual representation https://fun88chinhthuc.com/ scores by person over a 4-week average with drop-down options for supervisor/person and date selection, you can follow these steps:

Step 1: Organize Your Data

Ensure that your data is organized in a structured manner. You might have columns for "Date," "Person," "Supervisor," and individual columns for each test score. Make sure the data spans multiple weeks.

Step 2: Create Named Ranges

Create named ranges for your data to make it easier to reference in formulas. Highlight the relevant data range, and in the "Formulas" tab, click on "Create from Selection" to create named ranges for the columns.

Step 3: Design Your Page

Create a new sheet for your page. In this sheet, design the layout with drop-down menus for "Supervisor/All" and "Date" selection.

Step 4: Implement Drop-Down Menus

Use Excel's Data Validation feature to create drop-down menus for "Supervisor/All" and "Date" selection. This can be done by selecting the cells where you want the drop-downs, going to the "Data" tab, and choosing "Data Validation." Set the criteria based on your data.

Step 5: Formulate Averages

Use formulas to calculate the average scores for each person over the past 4 weeks based on the selected supervisor/person and date range. You can use functions like AVERAGEIFS to calculate these averages dynamically.

Step 6: Create Bar Graphs

Now, create separate bar graphs for each test score based on the calculated averages. Use Excel's charting tools to generate bar graphs. You can use the "Insert" tab and choose the appropriate chart type.

Step 7: Link Graphs to Drop-Downs

Link the drop-down menus to the graphs. This can be done by creating dynamic named ranges using the OFFSET and MATCH functions. The named ranges will change based on the selected options in the drop-down menus.

Step 8: Test Your Page

Test the functionality of your page by selecting different options in the drop-down menus. Ensure that the bar graphs update accordingly.

Step 9: Format and Finalize

Format your page for better visual appeal. Add titles, labels, and any other necessary details to make the page user-friendly.

Step 10: Review and Share

Review your dynamic page, and if everything looks good, you can share the sheet with others or print it as a part of your book.

Remember that the exact steps and functions used may vary based on your specific Excel version, but these general steps should guide you in creating a dynamic page with visual representations based on selected criteria.