Excel file sharing and collaborating

Hey,

I have an excel file that have one master sheet which combines data from 30+ other sheets. Each of the 30+ sheets has a template and needs to be fulfilled by a different person in charge. Since the data are confidential, each person in charge cannot know the data on the sheet that doesn't belong to their responsibility.

Right now what I am doing is quite manual: I break the file down and send each separate sheet to each person via email, then combine them all into the original file. The problem is this process takes a lot of time and whenever each person changes their data, I will have to change link on the master sheet.

I have tried sharing files on OneDrive to them, however the problem is there is no way to share each sheet to each person (you can only share the whole file to each person).

So is there any solution to share each sheet with each person and I don't have to combine data again when they change their data?

Thank you for your help!

Unfortunately not that is secure and reliable. A common approach is to use vba to automate the manual task of combining the workbooks.

You'd need to upload a sample for anyone to help using that approach:

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Sample file.xlsx

Thanks Kyle,

This is the simplified version of the file. So I will send each sheet to each department, they will fulfill their expense in the year and return to me. After that, I will manually link numbers from the original file to numbers in each submitted file.
This results in an excel file with links to 16 other files, and whenever each department changes their numbers (and this happens quite often), I will have to re-link the file.

So I am looking for a way to share the workbook to each department. However because of the confidentiality of the file, each department cannot see other department's number.

I wish you guys can help...
Thank you!

Guys, anyone can help?