Hi All,
Really struggling with getting my Dynamic Tier System working
I've built a Shipping line detention free time calculator which works out the number of free days based on a specific Contract selected. (Each contract & Shipping line has its own specific number of free days)
Cell "S1" is the contract which is Dynamic and will publish the number of free days against that specific contract # in Cell "R13"
Cells "P1:Q9" are dynamic based on Shipping Line / Container Type / Container Size.. this works out my Tier System.
Cell "R12" is a cell which I would populate manually (as this is something I am going to run daily)..
Initially I was going to work it via Formulas, Then I started going down this rabbit hole of coding it via VBA.. but I cant seem to get the number of days incurred working correctly. In some instances the Number of Free days could be 7 , 12 , 14, 21, 30 free days.
Any advice would be appreciated.
There are only supposed to be a maximum amount of days in each tier, but I have to also incorporate the number of free days. Example. If there are 12 free days.. then Day #1 of detention would start on day 13. .
Really struggling with getting my Dynamic Tier System working
I've built a Shipping line detention free time calculator which works out the number of free days based on a specific Contract selected. (Each contract & Shipping line has its own specific number of free days)
Cell "S1" is the contract which is Dynamic and will publish the number of free days against that specific contract # in Cell "R13"
Cells "P1:Q9" are dynamic based on Shipping Line / Container Type / Container Size.. this works out my Tier System.
Cell "R12" is a cell which I would populate manually (as this is something I am going to run daily)..
Initially I was going to work it via Formulas, Then I started going down this rabbit hole of coding it via VBA.. but I cant seem to get the number of days incurred working correctly. In some instances the Number of Free days could be 7 , 12 , 14, 21, 30 free days.
Any advice would be appreciated.
There are only supposed to be a maximum amount of days in each tier, but I have to also incorporate the number of free days. Example. If there are 12 free days.. then Day #1 of detention would start on day 13. .
Mondiale SLine Detention Rates 2022.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
O | P | Q | R | S | T | U | |||
1 | Contract | QACD037777 | |||||||
2 | Shipping Line | ANL | |||||||
3 | Container Type | HC | |||||||
4 | Container Size | 40 | |||||||
5 | DAY FROM | DAY TO | Number of Days Incurred | Cost Incurred | Per Day Amount | ||||
6 | 7 | 1 | 7 | $0.00 | $ - | <- Tier 1 | |||
7 | 7 | 8 | 14 | 7 | $770.00 | $ 110.00 | <- Tier 2 | ||
8 | 7 | 15 | 21 | 2 | $400.00 | $ 200.00 | <- Tier 3 | ||
9 | 978 | 22 | 999 | 0 | $0.00 | $ 250.00 | <- Tier 4 | ||
10 | |||||||||
11 | Total | ||||||||
12 | Number of days on Detention (Cell AB) | 9 | $1,170.00 | ||||||
13 | Number of Free Days | 12 | HC40 | ||||||
Data Table New Contract |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O6 | O6 | =Q6 |
Q6 | Q6 | =P7-1 |
S6:S7,S9 | S6 | =T6*R6 |
O7 | O7 | =(Q7-P7)+1 |
P7 | P7 | =VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,9,FALSE) |
Q7 | Q7 | =VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,10,FALSE) |
R7 | R7 | =MIN(O7,R12) |
T7 | T7 | =VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,11,FALSE) |
O8:O9 | O8 | =IFNA((Q8-P8)+1,0) |
P8 | P8 | =IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,9,FALSE),0) |
Q8 | Q8 | =IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,10,FALSE),0) |
R8 | R8 | =MIN(R12-R7,O8) |
S8 | S8 | =IFNA((T8*R8),0) |
T8 | T8 | =IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,11,FALSE),0) |
P9 | P9 | =(IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE)="",P8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE))),0)) |
Q9 | Q9 | =IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE)="",Q8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE))),0) |
R9 | R9 | =MAX((R12-O7-O8),0) |
T9 | T9 | =IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE)="",T8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE))),0) |
S12 | S12 | =SUM(S7:S9) |
R12 | R12 | ='C:\Users\joey.wright\Desktop\[detentionexport.xls]car_qry_detention'!$AB$17 |
R13 | R13 | =VLOOKUP($S$1,'Contract Numbers'!$B:$V,HLOOKUP('Data Table New Contract'!$U$13,'Contract Numbers'!$J$1:$V$2,2,FALSE),FALSE) |
U13 | U13 | =S3&S4 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
R9 | Whole number | between 0 and O8 |
S2 | List | ='Data Table'!$M$3:$M$17 |
S3 | List | ='Data Table'!$N$3:$N$8 |
S4 | List | ='Data Table'!$O$3:$O$5 |