SOLVED

Help with Linking information between main worksheet and another tab

Copper Contributor

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.

6 Replies
best response confirmed by Andreu2889019 (Copper Contributor)
Solution

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-70cfe6...
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?

yes, I do. I have 365. I have never used Vstack, my quick search of it - it looks a bit daunting. @Patrick2788 

I can step you through it. If you have a sample workbook that's comparable to the real workbook, I can draw up the formula.

@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. 

1 best response

Accepted Solutions
best response confirmed by Andreu2889019 (Copper Contributor)
Solution

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-70cfe6...
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.

View solution in original post