Background Information

The Wisconsin Partnership Program (WPP) has a webpage which shows off a number of the grants that the program has funded since its inception in 2004. This webpage is called the "Funded Projects Page" as it contains a number of projects that the WPP has funded.

The webpage can be found by following this link: https://www.med.wisc.edu/wisconsin-partnership-program/funded-projects/

On a yearly basis (usually in ~beginning to mid November), the WPP Communications team will reach out to the system analyst to ask for a new version of the report that is used the UW Web team to update the Funded Projects page. This page will be a guide for how to run/transform that report.

First Step: Run the Funded Projects Report in Fluxx

The first step is to find (or add to your dashboard) an Ad-hoc Reports card (with no filters applied to it). In the search bar for the card, type in "Funded Projects;" the only report returned should be the "Funded Projects Report." Click on that report name from the list in order to open up the record. Then, click on the "Workflow" button found in the bottom right-hand corner of the card. From the small menu that pops up, click "Generate Report" in order to run the latest version of this report. Please see also the screenshot below for more assistance.

Once you do so, you'll have to wait some time (usually ~30 seconds up to a minute) before the report downloads and can be opened. Download and open the report when it does so.

If the report opens in "Protected View," feel free to click on the "Enable Editing" button (see also the screenshot below).

Once open with editing enabled, the report should look something like this:

You've now completed the initial step of getting the data ready for the Funded Projects Webpage update. Please see the next section, "Making Changes to the Excel Sheet" to see what modifications are needed to the report before it is ready to be sent to the UW Web team.

Step Two: Making Changes to the Excel Sheet

Now that you have the report, it's time to get the file ready for import.

T-4 Sortable Element Column

We first need a "T4 Sortable Element" column. To create it, start by right-clicking on column A and then select "Insert" from the menu that appears; this will insert a new column to the right of column A.

In the new cell A1, type in the name for your new column. The name for this column should be "T-4 Sortable Element."

If you want to make the title more readable, you can select all cells in the sheet (click on the icon in the top left-hand corner of the cells) and then click "Wrap Text" on the "Home" tab.

Your cells should now expand (downwards). Now, click into cell A2 (highlighted in red in the screenshot below). In that cell, enter in the following formula: =CONCAT("[[sort]]
",B2). After you insert the formula in the cell, click the "Enter" button in order to finalize the formula.

Your formula should now be established in cell A1. It should read as "[[sort]] (line space) (Grant Number from the same row in column B)." Now, we need to populate the rest of the cells in column A. Move your cursor to the bottom right-hand corner of cell A2 until your cursor changes into a tiny black cross. When it does so, double click your mouse. This will populate all of the cells in column A with the formula you entered into cell A2. That will complete the creation of the T-4 Sortable Element column. Please also see the screenshot below for further instruction.

Before moving onto the next step, you can take a step that isn't necessary but is helpful, i.e. turning your spreadsheet data into an official table. To start, click in any cell that contains data, and then click Ctrl + A on your keyboard to select every cell that would make up your table.

Concatenate the Health Topics Columns into One

Next, scroll over to the right until you see the columns "Health Topics (OAC)" and "Health Topics (PERC)" (should now be columns T & U).

After that, right-click in column T (right above the header) and then click on "Insert" from the menu that appears. This will create a new cell to the left of the cell Health Topics (OAC).

In the newly created column, change the title of the column to "Health Topics." Then, click into cell T2. In that cell, enter into the following formula: =CONCAT(U2,V2). After you enter that formula in the cell, hit the "Enter" button on your keyboard to finalize the formula.

Now, move your cursor to the bottom right-hand corner of cell T2. When your cursor becomes a small, black cross, double click with the left mouse button. This will populate this formula into every cell in column T for your table.

Now, column T should be filled with a combination of what is in column U and column V. We need to make sure that this data, however, is stored as actual text and NOT just as an Excel formula.

To do this, start by clicking on cell T2 (your new Health Topics column, first cell down after the header). When that cell is selected, hold the Shift bar + Ctrl button and then click the down arrow (↓). This will allow you to select every (non-header) cell in column T.

Once you do so, you'll be brought down to the bottom of the table (highlighting every cell in column T on your way down – see also the screenshot below for how this will look). When you have everything selected, click Ctrl + C and then Ctrl + V.

Clicking Ctrl + C and Ctrl + V will copy and then paste the these cells back into column T. Doing so will bring you back up to the top of the table; towards the bottom of your screen, you should then see a button the says (Ctrl). Click on this button. From the menu that appears above the button, select the first icon underneath the Paste Values header (see also the screenshot below for visual assistance with this). This will ensure the values from these cells are pasted back in (rather than the formula).

This will ensure that the data is stable as text in column T. Now, we can delete columns U and V (Health Topics for OAC and PERC respectively, since they are now both combined into column T). To do so, click in column T right above the header, then Shift + Click into column U right above its header. Then, right click into other column above their respective header. From the menu that comes up below, click on the "Delete" option.

That completes the "Health Topics" column transformations.

Correct the Social Determinants of Health Column

The last column that needs changes is the new column V – which should now be called "Social Determinants of Health" (NOT to be confused with what should now be column W – "Social Determinants of Health Addressed.") The transformation in this column is relatively straightforward: the Funded Projects webpage can only accept single responses for this field, even though this field is multi-response.

The transformation is thus as follows: click into cell U2 and then click Ctrl + Down Arrow (↓) in order to find the next cell in this column populated with data.

If you find a cell that looks like the one found in the screenshot, below (where there is just 1 social determinant of health selected, i.e. just "Health and Healthcare") then you can leave it alone.

Continue clicking Ctrl + Down Arrow (↓) until you find a cell that looks like the one in the screenshot below. When you see a cell like this, you should delete the first comma in the text and ALL subsequent text after the first comma (so that, in the screenshot below, the cell reads only as "Employment"). This will ensure that the cell is now treated as a single-response field rather than a multiple-response field. Continue to clicking Ctrl + Down Arrow (↓) until you have transformed all cells like this into "single-response" fields.

That's it! The file can now be sent to the Communications team for the UW Web team to upload to the WPP Funded Projects Page.