I NEED IN VBA EXCEL. VBA EXCEL. VBA EXCEL. VBA EXCEL MULTIFORM Link to the data:

Question

I NEED IN VBA EXCEL. VBA EXCEL. VBA EXCEL. VBA EXCEL MULTIFORM

Link to the data:

https://docs.google.com/spreadsheets/d/1VQBeokg91Hje6DHZRLwfXBfr6BpxYbJJ7y9bfpoNs1w/edit?usp=sharing

***PLEASE GUIDE ME STEP TO STEP HOW TO CREATE THIS IN EXCEL WITH DASHBOARDS. SHOW ME HOW ITS DONE AND ILL RECREATE IT*** THANK YOU

Question 6. (20 Points) The Q6- BLS Employment Data worksheet presents some employment data Using dashboards in excel, (a) clearly state three, different substantive questions about unemployment that could be asked of your dashboard; (b) create a dashboard that allows the user to explore the data to glean meaningful insight about the data; and (c) shows the answers to your questions

Explanation / Answer

Steps to be taken as follows:-

Step 1: Create Custom Export in HQ for the project
1. Proceed to the Data section of CommCareHQ.
2. Select Excel Dashboard on the left-hand side.
3. See this Data Export Overview page for details on creating the data export:

4. Save the export

Step 2: Copy the Web Address of your saved export
1. In the main Exports section you will see your saved daily export.
2. Click Update Data button to generate the first report. You may need to refresh the page to confirm that the update is complete.
3. Select the Copy Dashboard Feed Link button.
4. Select the Copy Dashboard Feed Link button again to copy the URL. This will copy the URL of the export file so you can paste into Excel in the next step.

Step 3: Create the Excel Connection to your export
1. In Excel, Create a New File.
2. You will have a blank Excel file now.
3. Go to a blank tab in your workbook. Rename that sheet "Raw Data".
4. Select the top-left cell (A1).
5. Now we will pull in the data from the Excel file
6. Choose the Data menu
7. Choose From Web in the "Get External Data" section
8. Be sure you have Copy link address from step 2 above so it is on your clipboard.
9. The "New Web Query" window appears.
10. In the "Address" bar at the top, Paste the link you address you copied in Step 2 above. It should be something like
"https://www.commcarehq.org/a/excelreportingdemo/reports/export/saved/download/96c41fd6fb9be6119ad32467254cbc9a/"
11. Click Go
12. The "Windows Security" window appears. Type your CommCareHQ username and password.
13. The export will be displayed to you in the Web Query Window
14. Click the second Yellow Arrow above the table of data (not the very top one). This will select all the CommCare data in the export.
15. Click Import
16. The "Import Data" window appears.
17. Click Properties
18. The "External Data Range" window appears

19. Click OK again
20. The data is pasted into the empty worksheet

Step 4: Name your raw data for easy reference later
1. Now that your data is in the Raw Data tab, we want to name it so we can refer to the data later
2. Select all the columns that have data in them by clicking on the top of the columns while holding shift.
3. With the columns selected, click in the text box above column A (It likely says "A1" in it)
4. This is where you type the name of the raw data for reference later. Name it "raw_data".
5. Now, whenever you want to reference the raw data you can type "raw_data" in the formula instead of "='Raw Data'!A:E" like you normally would.

Step 5: Create the first Pivot Chart
1. Create a new tab called "Expected Delivery Date"
2. Select the top-left cell (A1) in this tab
3. In the Insert menu choose Pivot Chart from the PivotTable icon
4. In the window that pops up, click "Table/Range" text box
5. Type "raw_data" into the box. This references the data range we created in the step above and is a lot easier than selecting all the columns each time.
6. Click Ok.
7. This creates a blank pivot table. (If this doesn't work it's likely because the range is referencing only to the first tab. Go back to step 4.2, then click "Formulas" at the top, next to "Name Manager" click on "Define Name", enter "raw_data" under "Name" and make sure "Scope" is set to "Workbook" as this will ensure the reference can be used in different tabs, click OK)
8. Click the pivot table on the left
9. In the Field list on the right, do the following:

10. We now have the data we want but it needs to be formatted:
You can drag the graphical chart to the side to get it out of the way
11. Next, we need to display the dates as months instead of days
Right-click on any of the dates in the pivot table and choose Group
Select Months in the list the appears and choose OK
This groups all the EDDs into monthly buckets
12. The graph defaults to a bar chart, but you can change it:

13. Set the table to update automatically

14. Finally, rearrange the layout by inserting rows above table to move it down and drag the chart to the top of the page.
15. You can add titles and legends with the Layout menu
16. Now you get the final result.

Step 6: Create Additional Pivot Charts
1. Repeat all the steps in Step 5 as many times as you'd like to create more charts
2. In this example, see the screen shot for how the "Patients Received Vaccines" chart was made.
3. Be sure to set all table to update automatically (Step 15 in the section above)

Step 7: Create Filters
1. Excel uses "Slicers" to enable users to filter the reports in a user-friendly way.
2. For the first pivot table you created do the following:

3. Now we want this filter to apply to all charts in our Worksheet, so we connect the filters to the other tables by:

4. Copy and Paste the filters to other areas of your Excel sheet if you'd like them to appear in more than one place

Step 8: Build the Dashboard with Copy-Paste
1. Create a new tab called Dashboard
2. Copy and Paste the charts from other tabs in to the dashboard tab
3. Copy and Paste the filters you created, if you'd like
4. A same dashboard shows

Step 9: Hide the Raw Data
1. "Protect" all data

i. This will allow users to change filters but not edit the data
ii. Do NOT protect the "Raw Data" tab or the data will not update automatically
2. Hide the Raw Data tab (if desired)

3. Choose PivotTable option in Step 4

Step 10: Refresh Your Data
1. Choose Data menu
2. Click Refresh All
3. This will pull the latest data from CommCareHQ. You will be prompted for your password.

If Excel hangs during the refresh, disable Enable Background Refresh on all data connections. This is a known problem for spreadsheets with multiple web-based data connections. Find it under Data -> Connections -> Select Connection, click Properties -> unselect Enable Background Refresh.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.