MasterBash
New Member
- Joined
- Jan 22, 2022
- Messages
- 47
- Office Version
- 365
- 2021
- Platform
- Windows
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.
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.
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.