Linking between multiple sheets/workbooks

[MasterBash]

Hello all,

I am very lucky that a kind member was able to build an excel sheet for me to help to me synchronize data between different sheets. I really appreciate his help.
Unfortunately, the management did not approve. Not because the project I am working on isn't a good idea... They loved the idea, but it needs to be built differently.
It needs to answer a few questions :

Data security - Some clients can only have access to one or two sheets. Some others can only have access to one sheet. None of them should have access to our sheet. Since they have access to the online version of Excel, I think the only option is to use different workbooks and synchronize between those, instead of using sheets, because VBA scripts do not work on the online version.

Sustainability - How to make it hard to break or rather, how to fix it once an Excel sheet breaks ? How to stop it from breaking in the first place ? Maybe a button to fix all the formatting and tables ? In this case, a VBA script would be fine.

Maintenance - How to keep going when there is just way too much data ? How to archieve old data ? I think we can archieve data that are over a month old, to keep the sheets/workbooks as fast as possible.

You may ask why data gets repeated so much... There is some confidential information that some clients should not have access to, but some others should be able to access. Some of us also need access to that data on our sheet (Shipments). Right now, we use google sheets for almost everything, but none of those sheets are synchronized, so we even everything manually 3-4 times.

shipments_5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Load Type	Carrier	Trailer	Load #	Expected Date of Departure	PAPS	Port of Entry	Request paps/poe	Comments	Send e-mail to broker	Documents to broker	Entry #	Cleared Date	Cleared Time	Send e-mail to carrier	Actual date/time of Departure
3	STO	new carrier		7777
4	STO			9876		yes
5	STO			5555		no
6	STO			1234	02-01-24	p1	e1					9999
7	FTL	fedex		343434		ppppp	eeeee					enum
8	STO	cc	tt	656565		p1
9	ABC	c	t	l
Shipments

shipments_5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Load Type	Creation date	Load #	Trailer #2	Carrier	Status	Content	Weight	Seal	Expected Date of departure	Documents	Entry #	PAPS	Port of Entry	Reference	Carrier expected pick-up date/time	Actual date/time of departure
3	STO	06-07-24	1234			loaded				02-01-24		9999	p1	e1
4	STO	07-07-24	9876			loaded							yes
5	STO	29-06-24	4444			loaded
6	STO	06-07-24	5555			loaded							no
7	STO	29-06-24	6666
8	STO	29-06-24	7777			loaded
9	STO	07-07-24	656565	tt	cc	loaded
STO

shipments_5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Load Type	Creation date	Load #	Trailer #2	Carrier	Status	Content	Weight	Seal	Expected Date of departure	Documents	Entry #	PAPS	Port of Entry	Reference	Carrier expected pick-up date/time	Actual date/time of departure
3	FTL	06-07-24	343434		fedex	loaded						enum	ppppp	eeeee
4	FTL	06-07-24	12121212	2222	cccccc	loaded	cccc	www	sss	ed	dddd	eeee	pppp	p	rrrr	ccc	ad
FTL

This is a workbook. This is a short version of it, but we are looking to synchronize different time of data.

1. STO / FTL sheet changes

1a. If Load# is entered manually, then Creation date = Today. Expected data of departure is Creation date + 2 work days.

1b. if Status <> Loaded then do nothing

1c. If Status = Loaded then
Load Type = STO (Load type is telling us if its an STO or FTL), then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on Shipments sheet
Load Type = FTL (Load type is telling us if its an STO or FTL), then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on Shipments sheet

2. Shipments sheet changes

2a. if Load Type = STO, Load# exists on STO sheet then put Entry#, Actual date of departure to the corresponding line on the STO sheet
2a. if Load Type = FTL, Load # exists on FTL sheet then put Entry#, Actual date of departure to the corresponding line on the FTL sheet

2b. If Load Type = STO/FTL but load# does not exist on STO/FTL sheet, then do nothing

Currently this is the VBA scripts :



How it works :
Someone fills out the FTL or STO sheets with Load type, Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry, and once done (Status = Loaded). We get that information from the FTL or STO sheets on the Shipments sheet. After we are done processing everything and we fill out the shipments sheet with entry# and/or actual time of departure, send the entry# and actual time of departure back to the STO or FTL sheets.

If some information is missing (lets say trailer# on STO/FTL sheet) that is fine. The trigger to sync the data to Shipments sheet is Status = Loaded. If we are missing the entry# or Actual date of departure on the Shipments sheet, we can still sync the entry# or actual date of departure, even if one of the 2 information is missing.

We must be able to manually enter information in the Shipments sheet.

How do we accomplish that ? Is Excel really the best option ?

Thank you in advance for the help. I know this is a big project that I am working on, and I don't really know how to accomplish this. If needed, I can post the current VBA script as an example. However, I can't use VBA to synchronize data.

Once again, thank you ! I am taking any suggestions and recommendations. Like I said, maybe Excel isn't the best option in this case.

 

[MasterBash]

Bumping this thread, I am still interested to know if it is possible to do this and if yes, how to accomplish this.

I don't know if I am going the correct way with this, or if there are better solutions.

 

[MasterBash]

Posted here : Linking between multiple workbooks

Unfortunately, I don't even know if my project is possible without using VBA.