Nov 08 2022 12:40 PM
Nov 08 2022 12:40 PM
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.
Nov 08 2022 12:48 PM - edited Nov 08 2022 12:50 PMSolution
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.
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.
Nov 08 2022 12:56 PM
Nov 08 2022 08:55 PM
yes, I do. I have 365. I have never used Vstack, my quick search of it - it looks a bit daunting. @Patrick2788
Nov 09 2022 09:56 AM
Nov 10 2022 01:27 AM
@Patrick2788 I gave it a try and had no problem with it. The only thing I dislike is it is not being formatted as a table, but I’m sure I could easily fix that. When I tried vstack the other day I only had a few moments to mess around with it. So I’ll still have to work out the kinks. Thanks for your offer.