Excel: fill out worksheets from list

Question

So, I have a form for my employees where they can see their goals for the next year and some other payout related information.

I have the information for every employee in a seperate list (master data).

Now i want to generate a worksheet (even better a seperate file) for every single employee (that is one row in my master data) which looks like the form but the fields are filled with my values from the master data list.

Example Master Data

Example Form

Form for John

How could I achieve this. Do I need to write a makro for this? Should I code it with C#?

Or is it possible just with Excel Functions (Preferred so my colleagues who cannot code can also work with it) ?

I hope this is the right place to ask this question.

Thank you in advance!

Answer 1

I would like to suggest you the way to create, the Employee Personal Information on Sheet 2.

Follow these steps:

1. Create a Blank Format for Personal Data on Sheet 2.
2. In Cell B4, apply Data Validation, in Setting TAB select LIST.
3. Select Names from Sheet 1(Master Data), Excel will create drop Down List.
4. In Cell B5, for Age write this Formula =VLOOKUP($B$4,Sheet1!$A$4:$C$6,2,FALSE)
5. In Cell B6, for Pay write this Formula =VLOOKUP($B$4,Sheet1!$A$4:$C$6,3,FALSE)
6. In Cell D6, for Pay Per Month write this Formula =VLOOKUP($B$4,Sheet1!$A$4:$C$6,3,FALSE)

NB:

Your Employee Personal Sheet will filled by data & Looks like the Screen Shot.

For other Employee, go to Name Cell B4, Click Drop Down & pick other Name, you find Employee's Data will be Changed.

Note, If you want to create a Separate Sheet for other Employee then, NO NEED to CREATE DROP DOWN LIST, simply WRITE other Name in Cell B4 of Sheet 3 and repeat steps 4 to 6.

Hope this help you.