how to autopopulate cells based on values from other cells

Hi marbie
there could be many ways. it is better to upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

Hi marbie

find the attatch file. is it help?

Hi Marbie;

Nice spreadsheet so far. Good use of relative data validation dropdowns. If you produced this yourself, you have quite a bit of knowledge in excel, so I should be able to keep explanations high level.

However, before I can choose the right solution, I had a question on the requirements. Two things really:

1) Is Department important for choosing the proper Training? Or is it just the Competency/Type? If Department is irrelevant, then why is it included in the Training Matrix Worksheet?

2) When I reviewed the Training Matrix Worksheet, it appeared that there were many items that could fall under a single designation of a Competency and Classification. For example, a Quality of Product Management External could be:
Technical Writing and Documentation OR
Managing Change and Transition

Were you looking for a drop down menu to be made within the Final Form, or just an automatically populated lookup value? If just automatically populated, how would you select from the multiple entries in the Training Matrix table?


As a side note:

I can understand if additional information has been removed, but it's typically in poor practice to split/merge cells for reference data, and it can negatively impact the ability to do lookups, such as Column A in Training Matrix and Column A in Training Summary.


In Conclusion:
You can use =concatenate on the two criteria and use that as the value for a vlookup. It wouldn't require additional numerical values in your reference data, but it would require an additional column with a concatenation of the same two criteria in the reference data.

Ok, so each competency is unique, and then 3 different methods of training.

Unfortunately, a vlookup would just result in the first entry that meets the criteria. You would need to do another level of data validation, which would require a large reorganization of the data. Personally, I'm leaving on a nice long 18 hour or so flight. I've copied the sample spreadsheet, I'll see if I can put something together to minimize the manual work associated with the reorganization of data.

In terms of sizing and scoping, I know this is just a sample of 36 competencies with 158 training items, but could you give me an idea of the size of the live data?

Access would likely be easier. But that decision is up to you and your intended audience / user base.

Memo field type offers near limitless characters, but has some reporting and export problems in excess of 512 characters.

The current solution that I'll be looking into is writing up some VBA to migrate the data from the Training Summary page over into the training matrix page, and then create the necessary named ranges for the Training data validation.

Wonder if I can finish it before my battery dies.

Hi marbie

find the attatche file with formula for list of trainings

welcome

It is recommended to use evaluate formula option in the formula tab in order to understand the formula