Im trying to populate a Sharepoint list with the most upcoming dates from certain colums of data from another Sharepoint list?

Question

I have a list named Employee Dates, this list contains the columns:

Employee | CPR Completed | CPR Required | ETC

These columns keep going on for all of the training courses required for our employees with alternating columns for completed and required dates. I am using a workflow to calculate all of the required dates of training from the completed dates.

What I desire to do is make another list that will look at ALL of the columns for the required dates and find the soonest ones and populate that list with the soonest dates and from which column it was pulled from.

Any help as to how to approach this? I have been trying to use queries in Access and also some of the custom view settings in SharePoint Designer but no luck so far.

Answer 1

You could try an Excel table (they also have these functions in access if I recall, but I avoid access like a plague). To connect Excel to share point follow the steps in this article:

support.office.com

Ok, now that we are connected you should see all of the columns and values in excel. Next up we need to find the min date (easy) and then get the associated column name (a little harder).

• Min Date: The formula should be something along the lines of =min(B1,B3,B5), jut type in =Min( and then CTRL-click on the columns you want to consider for the row. When your done close with ). After wards double click on the square in the bottom right corner and it will do the same logic for all of the rows.
• Column Name: A little more difficult, use the min value from the prior column as the lookup value for VlookUp to get the column name. After wards double click on the square in the bottom right corner and it will do the same logic for all of the rows. I'd explain VlookUp, but I'd run out of characters and attention span long before I got to the relevant parts, and excel functions does a fine job of getting you the basics.

Anyway hope that helps,