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.

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.