Pulling data from multiple sheets

[MasterBash]

Hello,

I am working on a new project and I would like some help to pull data from multiple sheets.
I attached the file.31676

Shipments is the main sheet that we will be working on.
Shipments should be pulling data from STO and FTL sheets. In other words, I would like to pull those :
Load #, Trailer #, Carrier, paps, port of entry, Expected date of departure.

If some cells are empty (can't pull everything because some cells are missing data) I would like the text inside the cell to be TBD

The data that STO and FTL sheets need to pull from the Shipments sheet is this :
Entry #, Actual date of departure.

If some cells are empty (can't pull everything because some cells are missing data) I would like the text inside the cell to be TBD

Seal will be entirely pulled from another workbook.

How can I accomplish this ? In case I chose to use different workbooks instead of worksheets, I would like a formula that I use across workbooks too, if that is possible.

Thank you !

[Aussiebear]

Field
Sheet
Column #
Possible Sources


Load Type
Shipments
A
User Entry


Carrier
Shipments
B
User Entry, or Sheet ("STO") Column D or Sheet("FTL") Column D


Trailer
Shipments
C
User Entry, or Sheet ("STO") Column C or Sheet("FTL") Column C


Load #
Shipments
D
User Entry, or Sheet ("STO") Column B or Sheet("FTL") Column B


Expected Date of Departure
Shipments
E
User Entry, or Sheet ("STO") Column H or Sheet("FTL") Column H


PAPS
Shipments
F
User Entry, or Sheet ("STO") Column K or Sheet("FTL") Column K


Port of Entry
Shipments
G
User Entry, or Sheet ("STO") Column L or Sheet("FTL") Column L


Request paps/poe
Shipments
H
User Entry


Comments
Shipments
I
User Entry


Send e-mail to broker
Shipments
J
User Entry


Documents to broker
Shipments
K
User Entry


Entry #
Shipments
L
User Entry, or Sheet ("STO") Column J or Sheet("FTL") Column J


Cleared Date
Shipments
M
User Entry


Cleared Time
Shipments
N
User Entry


Send e-mail to carrier
Shipments
O
User Entry


Actual date/time of Departure
Shipments
P
User Entry


Creation date
STO
A
User Entry, or Sheet("FTL") Column A


Load #
STO
B
User Entry, or Sheet ("Shipping") Column D or Sheet("FTL") Column D


Trailer #
STO
C
User Entry, or Sheet ("Shipping") Column C or Sheet("FTL") Column C


Carrier
STO
D
User Entry, or Sheet ("Shipping") Column B or Sheet("FTL") Column B


Content
STO
E
User Entry, or Sheet("FTL") Column E


Weight
STO
F
User Entry, or Sheet("FTL") Column F


Seal
STO
G
User Entry, or Sheet("FTL") Column G


Expected Date of departure
STO
H
User Entry, or Sheet("FTL") ColumnH


Documents
STO
I
User Entry, or Sheet("FTL") Column I


Entry #
STO
J
User Entry, or Sheet ("Shipment ") Column L or Sheet("FTL") Column J


PAPS
STO
K
User Entry, or Sheet ("Shipment ") Column F or Sheet("FTL") Column K


Port of Entry
STO
L
User Entry, or Sheet ("Shipment ") Column G or Sheet("FTL") Column L


Reference
STO
M
User Entry, or Sheet("FTL") Column M


Carrier expected pick-up date/time
STO
N
User Entry, or Sheet("FTL") Column N


Creation date
FTL
A
User Entry, or Sheet ("STO") Column B


Load #
FTL
B
User Entry, or Sheet ("Shipping") Column D or Sheet("STO") Column B


Trailer #
FTL
C
User Entry, or Sheet ("Shipping") Column C or Sheet("STO") Column C


Carrier
FTL
D
User Entry, or Sheet ("Shipping") Column B or Sheet("STO") Column D


Content
FTL
E
User Entry, or Sheet("STO") Column C


Weight
FTL
F
User Entry, or Sheet("STO") Column F


Seal
FTL
G
User Entry, or Sheet("STO") Column G


Expected Date of departure
FTL
H
User Entry, or Sheet("STO") Column H


Documents
FTL
I
User Entry, or Sheet("STO") Column I


Entry #
FTL
J
User Entry, or Sheet("STO") Column J


PAPS
FTL
K
User Entry, or Sheet("STO") Column K


Port of Entry
FTL
L
User Entry, or Sheet("STO") Column L


Reference
FTL
M
User Entry, or Sheet("STO") Column M


Carrier expected pick-up date/time
FTL
N
User Entry, or Sheet("STO") Column N

[MasterBash]

Thanks Aussiebear,

That is almost correct. However, STO and FTL won't pull data from each other. They will pull data from Shipments only.

Seals data will be pulled off from an entirely different workbook. :)

If I know which formula to use, I can apply it myself to the sheets/columns.

[Aussiebear]

My post was simply to show that there are significant linkages already in place. Linkage which to me indicates that this project of yours should be in a database type program. You really should be talking to arnelgp about this stuff.

Currently you have 3 sheets with 44 used columns, yet when condensed down there are only 24 unique columns. Some will need to entered manually others can simply be linked to reflect the same value if we are to follow your concept. In my opinion this simply leads to unnecessary bloating of the workbook. Then you also mention that yet another workbook is involved. Pardon me, but I am somewhat reluctant to get involved as I believe that you are headed down the wrong path.

[MasterBash]

Yes, I am aware of that. At the moment, we enter everything manually up to 4 times. Unfortunately, it has to do with ease of use and authorizations.
Carrier X is allowed to see FTL sheet, but can't see the others. Carrier Y can see STO, but not the others. Client wants an easy to read sheet so we have the shipments sheet.

Everyone wants something different. The purpose of this is that instead of typing the same thing manually 4 times, we can type it once and it will be linked to the other sheets.

While Excel is quite easy to use, they may not understand something like Access.

[Aussiebear]

Excel is primarily for calculations and whilst can be used for small database type operations, eventually it fails due to being very memory intensive. Its your choice given that you say you are entering the same thing manually four times..... all the more reason why this method is a poor one.

[Paul_Hossler]

It sounds to me like you want to 'synchronize' data between

Shipments <--> STO
Shipments <--> FIL
presumably using Load# as the 'key'

I made a visual map as to where the data goes and where it comes from

31677

31678

31679


The data that STO and FTL sheets need to pull from the Shipments sheet is this :
Entry #, Actual date of departure.


However, the field "Actual date of departure" is not on STO nor FIL, but it is on Shipments, so where does it go?

[MasterBash]

I understand what you are saying and I agree with you. However, it isn't really my choice. That is what they want us to use. All I can do is to make it easier for them to use those Excel sheets. I wish things would be on Microsoft Access, but that is not an option.

Even tho it seems like a better option, I also don't know if Access can support autofill for cell by simply dragging, stuff like e-mail with subject, body, signature and linkage like in my initial post. I think it would take a while for me to learn Access, if it would be an option.

[MasterBash]

It sounds to me like you want to 'synchronize' data between

Shipments <--> STO
Shipments <--> FIL
presumably using Load# as the 'key'

I made a visual map as to where the data goes and where it comes from

31677

31678

31679

However, the field "Actual date of departure" is not on STO nor FIL, but it is on Shipments, so where does it go?

Thank you ! Actually there should be an "Actual date of departure" in STO/FTL sheet. If we have a STO load, then actually time of departure should be written in the STO sheet. If Load is a FTL load, then Actual time of departure should be in the FTL sheet. Everything depends on the Load (and order type STO/FTL).
Indeed, synchronization between different sheets is what I am trying to accomplish. :)

I am looking into this right now.

Added the attachment with some edits. :)

31682

Thank you !

[Paul_Hossler]

Everything depends on the Load (and order type STO/FTL).

There is no Order Type field, so I assume that Load# on Shipment would be used to find the data on either on STO or on FIL, and that Load# on STO or FIL would be used to find Shipment data??

[MasterBash]

Exactly.

In other words, Load Type (sorry not Order Type) is simply where it comes from (STO or FTL sheet)
If load comes from STO sheet, then the load type is STO. :)

I added Load Type on STO/FTL to make it easier.

Please see attached file. I added Load Type on the other sheet, if it makes it easier to sync data.

The Trigger for loads to sync to Shipments is Status (Must be Loaded). If it is in-progress or anything else, then data must not be sent to Shipments.

[Paul_Hossler]

So ...

1. Shipments sheet changes

1a. Use Shipments Load#

1b. Find Load# on STO or FIL depending on Load Type (or add if not there)

1c. if Load Type = STO, then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on STO
1c. if Load Type = FIL, then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on FIL

1d. Make Creation Date = today


2. STO / FIL sheet changes

2a. if Status <> Loaded then do nothing

2b. Find Load# on Shipments (or add if not there)

2c. Update Shipments with Trailer #2, Carrier, Expected Date of departure, Entry #, PAPS, Port of Entry

[MasterBash]

So ...

1. Shipments sheet changes

1a. Use Shipments Load#

1b. Find Load# on STO or FIL depending on Load Type (or add if not there)

1c. if Load Type = STO, then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on STO
1c. if Load Type = FIL, then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on FIL

1d. Make Creation Date = today


2. STO / FIL sheet changes

2a. if Status <> Loaded then do nothing

2b. Find Load# on Shipments (or add if not there)

2c. Update Shipments with Trailer #2, Carrier, Expected Date of departure, Entry #, PAPS, Port of Entry

It is actually FTL, not FIL. :)
I am sorry if my english is not perfect. Please allow me to start with #2

2. STO / FTL sheet changes

2a. If Load# is entered manually, then Creation date = Today

2b. if Status <> Loaded then do nothing

2c. 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

1. Shipments sheet changes

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

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

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.

[Paul_Hossler]

You can play with this and see

I'm sure there's some error conditions I don't handle and I only moved the fields that you specifically mentioned

[MasterBash]

Thank you so much, Paul !

I am currently on vacation, I will get back to you next weekend. I will not forget to mark this thread as solved if everything is good.

Thanks again. I appreciate it. :)

[MasterBash]

You can play with this and see

I'm sure there's some error conditions I don't handle and I only moved the fields that you specifically mentioned

Thank you once again, Paul ! :)

Few things :
Paps/port of entry from STO sheet does not seem to transfer to Shipments sheet.
If Status is set to Loaded (by accident or any other reasons) then switched back to something else, the line does not disappear from Shipments.

Can this code be applied across workbooks if I were to make those sheets into different workbooks instead of multiple sheets in one workbook ? Most people will be using the online version and only use the excel app on windows to make modifications. Will synchronisation occur ?

Thanks. :)

[Paul_Hossler]

1. Paps/port of entry from STO sheet does not seem to transfer to Shipments sheet.

2. If Status is set to Loaded (by accident or any other reasons) then switched back to something else, the line does not disappear from Shipments.

3. Can this code be applied across workbooks if I were to make those sheets into different workbooks instead of multiple sheets in one workbook ? Most people will be using the online version and only use the excel app on windows to make modifications. Will synchronisation occur ?


1. I think they do

2. OK

3. Lot of work (too much)


Many col header don't match between Shipments and STO/FTL, so I guessed

This is the code that copies to Shipments so you can add or modify as necessary



'then put Carrier, Trailer, Load #, Expected Date of Departure, PAPS, Port of Entry onto new line on Shipments sheet
wsShip.Cells(n, 1).Value = ws.Cells(r, 1).Value
wsShip.Cells(n, 2).Value = ws.Cells(r, 5).Value
wsShip.Cells(n, 3).Value = ws.Cells(r, 4).Value
wsShip.Cells(n, 4).Value = ws.Cells(r, 3).Value
wsShip.Cells(n, 5).Value = ws.Cells(r, 10).Value
wsShip.Cells(n, 6).Value = ws.Cells(r, 13).Value
wsShip.Cells(n, 7).Value = ws.Cells(r, 14).Value

wsShip.Cells(n, 11).Value = ws.Cells(r, 11).Value
wsShip.Cells(n, 12).Value = ws.Cells(r, 12).Value
wsShip.Cells(n, 16).Value = ws.Cells(r, 17).Value

[MasterBash]

Thank you, Paul !

I just noticed one more thing. When I try to manually add data to Shipments, lets say I add a Load Type, or any other data that does not belong to STO or FTL sheets, I get Run-time error '9': Subscript out of range. Is it possible to allow writing without giving any errors ?

[Paul_Hossler]

Try this

[MasterBash]

This works ! :)

Thank you so much !