Office 365 Excel Workbook - share only one worksheet

OK, that is not good. 

Do you know if you can reference the contents of cells in a shared Workbook to another different shared Workbook on OneDrive.  I just need data from a series of different Excel files summed in a different Excel file.

Hi Douglas,

Please try the following suggestion to reference the content of cells from one workbook to another in OneDrive for business.

1. Sync both the workbooks in to your OneDrive folder.
2. Open the Excel workbook in which you want to reference the content.
3. Go to Data>New Query>From File>From workbook

       4.Navigate to the local OneDrive folder and select the workbook> Click Import.

       5.This will open the Navigator window and then select the sheet which you wish to refer.

      6. Select load>Load.

Once the spreadsheet is referenced, you can share the Workbook from OneDrive for business.

Let me know if this helps.

Thanks,

Neha

Hi Douglas,

Did you refer to my last reply?

Regards,

Neha

Very clever Neha. :)

@doug929: The only downside to Neha's solution is it's not truly a shared workbook, but a read-only version of the specified data - and a snapshot in time, unable to be edited. If that's all you want, then this will work just fine. The alternative is to code a custom solution with VBA, use a database, or you could even create an Office Add-In to do this. In any case they get fairly complicated. 

I agree with Zack that this is only a snapshot and does not really work the way I want. I want to just reference some totals in another workbook that all would reside on OneDrive in Office 365. I think this is a common practice in the world of Excel, what is everyone doing who are trying to migrate to OneDrive?

Your options are limited if you use Excel. Right now you can share a workbook as a whole. That means if you want different people seeing different parts of a workbook, you would have to create their own workbook and share it with them. IMO this isn't a feasible option as it creates too much confusion and fractures data, which will lead to multiple issues down the road.

Another option would be to keep the data in a hidden sheet and use formulas to pull the data. For this option it's basically read only. Updating or adding new data becomes an issue without letting the user see the hidden data.

In vanilla Excel I would use VBA. For this you wouldn't be able to use Excel Online, but would need to use desktop Excel. For this you'd need to define things like where the input data resides, where the output data resides, how updating of information should happen, where new data would go, how you want users to access the data, Excel version, operating system, things like that. It's not that we can't do that, but the scope of it is starting to grow and might be more than can be handled on a single forum thread.

While the Excel team is constantly working on feature parity across the different versions, and the Online versions have taken leaps and bounds over the last few years, you're completely right there's a big feature difference between desktop Excel and Excel Online.

To be fair, when Microsoft says there's something new with Excel, they're primarily talking about desktop Excel, the main version. Right now the other flavors are basically runners up, as they don't have all the same features, but desktop has them all. Also, Office 365 refers to desktop Excel as well, not Excel Online.

Here are some resources for what's new regarding Office:

• What's new in Excel
• Excel TechCommunity

Microsoft posts updates to all Excel endpoints in the latter link, but honestly it can be difficult to find. And with the current model [of software], updates are pushed out at a much faster cadence than they were previously, sometimes monthly. If you want to be heard, there is a platform for that as well, Excel Uservoice.