collegeitdept
Board Regular
- Joined
- Nov 14, 2008
- Messages
- 185
I need a formula that can lookup (and match the due date to the column heading weekday of top row in a worksheet calendar) a store in a data table and return it's product shipping date & time in the relevant column in a worksheet calendar.
Data Table:
In the data table, the store numbers are in column A. The product shipping days are in the first column in a 3 column group (store's may have multiple shipping days). The shipping times are in the 2nd column of the group. The shipping Due days are in the last column of the group (text is in red).
Worksheet Calendar:
This is where formulas will auto-populate the shipping days for each store. It will populate the shipping days in the proper column (Weekday) by matching it to the Due Days in the data table.
(We are only interested in due dates from December 18 to December 29)
For every weekday column (Monday - Friday) there are three columns:
- 1st Column: Shipping day
- 2nd Column: Shipping Time
- 3rd Column: Due Day (last day to ship).
Ex:
For store # 1401 - there are 3 order days for this store (between December 18 - December 29).
In the Monday column, this store does have shipping order for Wednesday 12/24 (1st column) at 4:30AM (2nd column) - because the due day is Monday (3rd column).
This 3rd column in every weekday group is what must be matched to the Due day in the data table.
Tuesday is blank because this store doesn't have a shipping order that's due on Tuesday.
In the Wednesday column, this store does have shipping order for Saturday 12/27 (1st column) at 3:30AM (2nd column) - because the due day is Wednesday (3rd column).
In the Thursday column, this store does have shipping order for Saturday 12/20 (1st column) at 3:30AM (2nd column) - because the due day is Thursday (3rd column).
For the Friday column, the store does not have an order that's due. However, this and all the other stores do not process shipping orders on the weekend (Saturday and Sunday).... so if a shipping order has a due day of Saturday or Sunday, it will be treated as a due day of Friday (and placed in the Friday column group).
I would like a formula that lookup and match the Due Day to the column heading (weekday) of a store.... (function will be probably have to be in the 3rd column (Due day) of each weekday). The first two columns (shipping day and time) will probably be use the offset formula.
I will modify the formula to for the other weekday columns.
Data Table:
In the data table, the store numbers are in column A. The product shipping days are in the first column in a 3 column group (store's may have multiple shipping days). The shipping times are in the 2nd column of the group. The shipping Due days are in the last column of the group (text is in red).
Worksheet Calendar:
This is where formulas will auto-populate the shipping days for each store. It will populate the shipping days in the proper column (Weekday) by matching it to the Due Days in the data table.
(We are only interested in due dates from December 18 to December 29)
For every weekday column (Monday - Friday) there are three columns:
- 1st Column: Shipping day
- 2nd Column: Shipping Time
- 3rd Column: Due Day (last day to ship).
Ex:
For store # 1401 - there are 3 order days for this store (between December 18 - December 29).
In the Monday column, this store does have shipping order for Wednesday 12/24 (1st column) at 4:30AM (2nd column) - because the due day is Monday (3rd column).
This 3rd column in every weekday group is what must be matched to the Due day in the data table.
Tuesday is blank because this store doesn't have a shipping order that's due on Tuesday.
In the Wednesday column, this store does have shipping order for Saturday 12/27 (1st column) at 3:30AM (2nd column) - because the due day is Wednesday (3rd column).
In the Thursday column, this store does have shipping order for Saturday 12/20 (1st column) at 3:30AM (2nd column) - because the due day is Thursday (3rd column).
For the Friday column, the store does not have an order that's due. However, this and all the other stores do not process shipping orders on the weekend (Saturday and Sunday).... so if a shipping order has a due day of Saturday or Sunday, it will be treated as a due day of Friday (and placed in the Friday column group).
I would like a formula that lookup and match the Due Day to the column heading (weekday) of a store.... (function will be probably have to be in the 3rd column (Due day) of each weekday). The first two columns (shipping day and time) will probably be use the offset formula.
I will modify the formula to for the other weekday columns.
