Hello,

Would someone please help me design a relatively simple macro? I have a database of approximately 30,000 employees, listed by badge #. Some of the employees are occasionally run through assessments, which then requires my team to add the new assessment dates to their records. The fields to track the assessment dates are already in place.

See sample worksheet (attached), which gives the basic idea for the functionality I’d like.

The macro would:

1. Read the badge # and the Assessment Type from the “Dates to add” table
2. Find the matching Badge # in the database and paste the new Assessment Date into the correct column

…unless you have a better idea to update the database! I'm all ears.

Help greatly appreciated!

Attached Files

Database example.xlsx (11.6 KB, 11 views)

Here you go!

No need for VBA - worksheet functions will do nicely!

NB! One formula is an array formula (Cntrl+Shif+Enter to enter the formula when editing).

Attached Files

Assessments.xlsx (14.8 KB, 7 views)

In this version, Badge's for report are queried from Assessment sheet, and the report table is expanding/shrinking automatically. The query uses a fixed named range as source, and is created from menu Data > From other Sources > From Microsoft Query with Excel Files* as data source.

To eliminate the need of editing the query, I added an Open event, which adjusts query automatically, whenever workbook is moved or renamed. Having an Open event in workbook was the reason for change of workbook format to xlsm. To get the query to work, the workbook must be saved somewhere.

The query is refreshed automatically whenever the workbook is opened. During session you have to refresh it manually, when the need arises.

Attached Files

Assessments.xlsm (25.2 KB, 11 views)

Wow, interesting solutions! Thank you both so much! I'll play with both of these to see which suits best going forward. I appreciate your time and effort! Happy 2018