Excel Automation: Retrieving Previous Data Based on Dropdown Selection and Row Reference

In a training program workbook, I have dropdown menus containing exercises in column B. Data for each exercise is tracked in the green columns (O:Q), and what was previously done for each exercise is recorded in the purple columns (S:U). The goal is to automate the process of populating the purple columns based on the previous entries dynamically.

Requirements

1. Search for Exercise: Excel needs to search for a selected exercise from the corresponding dropdown menu in column B.

2. Retrieve Previous Data: Once a match has been identified (two identical exercises), Excel should search the green columns to retrieve the previously entered data for that exercise.

3. Order of Retrieval: If there are multiple entries for the same exercise, Excel should prioritize the closest entry to a reference row. For instance, if the reference row is 19, and there are entries at rows 24 and 29 for the same exercise, Excel should output the data from row 24.

4. Handling Gaps: The formula needs to skip or ignore cells that do not contain the relevant cell ranges.

My current formula addresses requirements 1-4 successfully.

Please Login or Register  to view this content.

Remaining Challenge:


Cross-Sheet Functionality: The automation should work across multiple sheets, ensuring that the previous week and all its rows are accounted for in the formula. In essence, the formula should search for matches in both Week 1 and Week 2 and prioritize Week 2 when there are matches in both weeks.

Sk?rmbillede 2024-03-16 kl. 20.43.36.png

I've added annotations and pictures to assist in understanding the structure of the workbook.

Originally Posted by DJunqueira

How much many potential weeks are we talking about here?

5 weeks

Depends on the program, but for simplicity assume 5 weeks.