Forum Discussion
Help with Linking information between main worksheet and another tab
Hi, the local non-profit I work for was allowed to host a state conference in 2024, so fundraising is a huge part of this- I have been tasked with organizing all the data that comes with potential donors and sponsors. my team is tasked with creating that list and contacting all of them. I have set up a workbook that has a main worksheet that has all of our contact data. this worksheet is locked so I can control who changes what on this page.
I have created a separate worksheet for each of my team members to edit and enter the information from the contacts they were assigned.
I am trying to find a simple way to link the information back to the main worksheet, as I update the contact information.
I have the main worksheet where I can filter it by each team member's responsibility. every time I try and copy and paste a team member's worksheet onto the filtered main worksheet, it says it's not possible.
T.I.A.
Depending on your version of Excel there are a few ways to consolidate multiple sheets.
1. VSTACK - available in 365. An elegant way to rollup data quickly.
2. PowerQuery
https://support.microsoft.com/en-us/office/learn-to-combine-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d
Very efficient. Potential drawback: you'd have a live query in your workbook and would need to get your team up to speed on keeping the query up to date and basics of using a query.
3. VBA - a macro could consolidate the sheets quickly. There's a question of how you'd run the macro (Clicking a button or on a specific event like sheet activation, for example). You'd have to get your team up to speed on working with an XLSM, enabling content, etc.Of the three above, I prefer VSTACK because it would involve 1 formula put in 1 cell and pulling back many records. No security or query issues. It's only a matter of availability.
Depending on your version of Excel there are a few ways to consolidate multiple sheets.
1. VSTACK - available in 365. An elegant way to rollup data quickly.
2. PowerQuery
https://support.microsoft.com/en-us/office/learn-to-combine-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d
Very efficient. Potential drawback: you'd have a live query in your workbook and would need to get your team up to speed on keeping the query up to date and basics of using a query.
3. VBA - a macro could consolidate the sheets quickly. There's a question of how you'd run the macro (Clicking a button or on a specific event like sheet activation, for example). You'd have to get your team up to speed on working with an XLSM, enabling content, etc.Of the three above, I prefer VSTACK because it would involve 1 formula put in 1 cell and pulling back many records. No security or query issues. It's only a matter of availability.
- Thank you very much for your response. I believe the first option might be best in my case, as most of the others working on my team are not that tech savvy, most are quite up in age, and are used to doing things via paper only.. so I have a few challenges on my hands but we will work through them. ;D
- Do you have access to the VSTACK function?
