How to merge data in excel from a master sheet to several sheets? (Microsoft 365)

[Rich1945]

Hello,

I am working on an end-of-year compensation project and need some suggestions to make this project easier and error-free.

I have a master Excel sheet that has employees by ID#, names, and all their compensation data.

I have created a template on the second sheet that will need to be populated for each employee with their annual compensation from the master sheet.

Since there are 650+ employees, typing the data manually will take forever.

I can't figure out how to use VLOOKUP for this task. All videos I found show how to merge data from multiple sheets to one, not the other way. Is there a way Excel can automate populating all employee individual sheets with data from the master sheet?

Thank you very much in advance.

[#2]

You need VBA to automate this tasks.

[#3]

https://answers.microsoft.com/en-us/msoffice/forum/all/split-data-into-multiple-excel-workbooks-base...

this？

[#4 · Accepted solution]

In the first cell where you want the compensation data on the employee sheet, use the following formula:

=VLOOKUP(A2, 'MasterSheetName'!$A$2:$Z$1000, 2, FALSE)
Adjust the ranges and sheet names accordingly. This formula looks up the ID# in the master sheet and retrieves the corresponding compensation data.

Drag/Fill Formula:
Drag the fill handle (small square at the bottom-right corner of the cell) down to copy the formula for all employees. Excel will automatically adjust the cell references.

Repeat for Other Data:
Repeat the process for other compensation data columns by adjusting the column index number in the VLOOKUP formula.

Data Validation (Optional):
To prevent errors, consider using Data Validation to ensure that each employee's ID# on their sheet matches the master sheet.

[#5]

@Harun24HR Thanks but I am not familiar with VBA.

[#6]

Not sure this is what I need in terms of the tasks in question.

[#7]

Thank you SO MUCH! This is exactly what I was looking for! I just did it and it worked. Apparently, the "FALSE" command had a huge role in here as I was getting the wrong data without it when I was trying it. I followed the same process for all the cells I wanted filled with specific data. After finishing the first sheet for the first employee, I will just make copies for the remaining employees and the data of their sheets will follow their unique IDs. Thanks so much again!!