Forum Discussion
Connecting Two Data Sheets
Hey everyone, I'm trying to figure out a solution for data tracking across two spreadsheets. I work in a higher education office, and we use an Excel spreadsheet (via Excel Online) to track events which our staff are holding. In order to put together a dashboard on Tableau for analyzing attendance, however, I need to use a different spreadsheet with specific formatting (which is not user-friendly for the advisors, and we have had issues in the past). I had the idea to use a Power Query to automatically update my attendance spreadsheet with event information from the planning sheet. Then, the columns where I track attendance would exist only on the tracking spreadsheet and not be connected to Query.
This has worked okay, but recently I realized that if a staff member adds an event in the past, the cells containing event info and connected to the query will shift down but the attendance ones won't, meaning that the attendance numbers are now in the wrong row.
Does anyone have suggestions for how I might be able to connect my planning spreadsheet with my tracking spreadsheet? At this point I'm not sure if we should just keep it all on a single spreadsheet, or if there's a good way to connect them. Thanks for any advice!
1 Reply
If you're primarily using Excel Online, you can achieve your goal by utilizing Excel Online's collaboration features. Here's how you can collaborate and link data between Excel Online spreadsheets:
- Share Both Spreadsheets:
- Make sure both your event planning sheet and attendance tracking sheet are saved in a location accessible to all team members, such as OneDrive or SharePoint.
- Collaborative Editing:
- Share both spreadsheets with your team members.
- Multiple users can collaborate on the same sheet simultaneously.
- Connect Data between Sheets:
- In your attendance tracking sheet, use a formula to link data from the event planning sheet. For example, you can use VLOOKUP, INDEX-MATCH, or simple cell references.
Example VLOOKUP formula for linking data from the event planning sheet:
=VLOOKUP([Common Identifier], [Event Planning Sheet Range], [Column Number], FALSE)
Formula is untested.
Replace [Common Identifier] with the cell containing the identifier you're matching on, [Event Planning Sheet Range] with the range in the event planning sheet that contains the data you need, and [Column Number] with the column number that contains the data you want to pull.
- Real-Time Collaboration:
- As your team members make updates in the event planning sheet, the linked data in the attendance tracking sheet will automatically update in real-time.
By using Excel Online and collaborative editing, you can maintain data consistency and real-time updates between your event planning and attendance tracking sheets, even if rows are added or modified. This approach doesn't require Power Query or Power Pivot and can work effectively for smaller teams or simpler data tracking scenarios. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
