Office 365, One drive, Excel, Replication of sheet from one excel file to another

Copper Contributor

Hi, my question is:
Is it possible to create multiple excel files in Onedrive using Office 365 but with one special criteria :) .
I will explain in details what I want to achieve :)

For example I create 4 excel files in Onedrive using Office 365 in our organisation.
For example:
File 1 is NewYork.xlsx
File 2 is Chicago.xlsx
File 3 is Texas.xlsx
And file 4 General.xlsx

The file 4 General.xlsx contains  3 sheets NewYork, Chicago and Texas with general information about all 3 sites,New York, Chicago and Texas VLAN segmentation :)
But the the first sheet of all site files NewYork.xlsx,  Chicago.xlsx and  Texas.xlsx they are the same in file 4 General.xlsx.
Example:
Sheet 1 from file 1 NewYork.xlsx is the same like sheet 1 in file 4 General.xlsx.
Sheet 1 from file 2 Chicago.xlsx is the same like  sheet 2 in file 4 General.xlsx.
Sheet 1 from file 3 Texas.xlsx is the same like sheet 3 in file 4 General.xlsx.

And my big question is it possible to do relation between all these 4 files ?

If I change data in file 4  General.xlsx Sheet 1, automatically change the data in sheet 1 in file 1 NewYork.xlsx?

And so If I change data in file 4  General.xlsx Sheet 2 , automatically change the data in sheet 1 in file 2 Chicago.xlsx?

And so  If I change data in file 4  General.xlsx Sheet 3, automatically change the data in sheet 1 in file 3 Texas.xlsx ?


I know it is possible to  do dynamic update data between different files and sheets but with Google.
Is it possible with Microsoft Excel with Office 365 and One drive ?

Thank you in advance :)



6 Replies

@Taner_Rasim Hi, did you find any work around? Understand that Excel has nothing similar to importrange or query, but would like to see if there's a workaround since I need to refer to several files within one sheet & update it in real time

 

Thanks

@opj5000 

Hello, unfortunately I didn't find solution with office 365 :( 

You can absolutely do this with references in the files, probably just in the general file really. You can reference each of the other files with a formula as long as you create the reference while the file is open from SPO.

Depending on how you are representing the information, rather than using Excel, you could also use MS lists or similar tools to store this data easier.

@SeanMcAvinue 

Hello, could you tell me more details with examples?

Office 365 is in the cloud and the storage there is One drive.

Really it is possible?

I just want to put data in 5 different excel files in their first sheet and I want to see all these sheets from 5 files in on a new excel file with all 5 sheets from previous files.