office365 – Excel sheet to read data in real time from *shared* spreadsheet

I have an online quiz setup using Google Sheets, and I want to transfer it to Excel. Here’s what I have:

Team1,…,Team9 have their own ‘Answer Sheets’ which are Sheets with a cell, A2, where they input their answers. I have a ‘Master Sheet’ which simply has a column that imports the data from A2 from answer sheet 1, answer sheet 2, etc. The column is:

=IMPORTRANGE(“URL of Team1 answer sheet”,”Sheet1!A2″)

=IMPORTRANGE(“URL of Team9 answer sheet”,”Sheet1!A2″)

This means:

  1. Teams cannot see answers submitted by other teams.
  2. I can see everyone’s answers in real time. (Okay, not strictly real time, but only a delay of a few seconds at the most.)
  3. Teams don’t need to download additional software; they just open the Sheets in their browser.

All three are essential for the quiz to run smoothly.

Question: Is it possible to set this up using Excel? All teams have access to Office 365 but I cannot rely on teams being able to download the spreadsheets to use the Excel desktop app. Things I’ve tried / ideas I’ve had:

A: My Master spreadsheet contains links to other answer sheets, which I have then shared. The problem is that when someone else inputs their answer on their answer sheet, it doesn’t update on the Master sheet. I have to close and reopen the Master sheet, but even then this often produces errors associated with links being out of date.

B: (Haven’t tried this but I have doubts it’ll work) My Master spreadsheet is shared with everyone, and I have one worksheet per team, but I change the access so that Team1 only has access to worksheet 1, etc. Then, there is a worksheet only I have access to which reads the answers from all the worksheets. This will work, I think, but two issues: I want to scale this to 30+ teams and in my experience Excel shared sheets become very slow when so many collaborate on them at once. Also, it sounds like a nightmare to set up, changing permissions so every team has a unique point of view on the workbook.

Is it possible to accomplish what I’m trying to do?

I love Excel, but it’s sharing functionality in many respects seems to be far inferior to what Sheets is capable of at times.