Hi
Here is an example, how I would do it
Create a sheet Articles, with a table on it (headers in row 1)
Article, Density
Enter into this table your products and their densities.
Define named ranges
Articles=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,1)
ArticleTbl=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,2)
Create a sheet Compartments, with a table
CompartmentCode, Size, MaxWeight
Enter all your possible compartment types and their parameters into table,
like
front1 9000 11273
front2 8700 10030
middle1 18000 20000
etc. Compartments codes in table are unique.
Define named ranges:
Compartments=OFFSET(Compartments!$A$2,,,COUNTIF(Compartments!$A:$A,"<>")-1,1
)
CompartmentTbl=OFFSET(Compartments!$A$2,,,COUNTIF(Compartments!$A:$A,"<>")-1
,3)
Create a sheet Configurations, with a table
ConfigurationCode, FrontCompCode, FrontCompNum, MiddleCompCode,
MiddleCompNum, BackCompCode, BackCompNum
(I assume here, that compartments in same section are identic, i.e. you
don't have p.e. 2 different front compartment in same train. When otherwise,
then the design complicates somewhat, but the pattern remains same)
For cells in columns FrontCompCode, MiddleCompCode and BackCompCode
implement data validation list with source
=Compartments
For cells in columns FrontCompNum, MiddleCompNum and BackCompNum you can
implement data validation list with source like
=1,2,3
Enter all possible train configurations you use at moment. As code use some
easy-to read abverration, like "Front2(2)Middle1(3)Back2(1)"
Define named ranges
Configurations=OFFSET(Configurations!$A$2,,,COUNTIF(Configurations!$A:$A,"<>
")-1,1)
ConfigurationTbl=OFFSET(Configurations!$A$2,,,COUNTIF(Configurations!$A:$A,"
<>")-1,7)
Create a sheet p.e. Transports (this will be your main working sheet), with
a table (header row is row 2)
Date, Transport, Configuration, Article group (group name in merged cells on
row 1) with columns Front, Middle, and Back, Quantity group with same
columns + Total, Weight group, again with same columns + Total, and
optionally the group '% from max weight, again with columns Front, Middle,
Back and Total.
For Configuration column, implement data validation list with source
=Configutations
For Article column, implement data validation list with source
=Articles
Define named ranges
BackArticleDensity=VLOOKUP(Transports!$F3,ArticleTbl,2,0)
BackCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,6,0)
BackCompMaxWeight=VLOOKUP(BackCompCode,CompartmentTbl,3,0)
BackCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,7,0)
BackCompSize=VLOOKUP(BackCompCode,CompartmentTbl,2,0)
FrontArticleDensity=VLOOKUP(Transports!$D3,ArticleTbl,2,0)
FrontCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,2,0)
FrontCompMaxWeight=VLOOKUP(FrontCompCode,CompartmentTbl,3,0)
FrontCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,3,0)
FrontCompSize=VLOOKUP(FrontCompCode,CompartmentTbl,2,0)
MiddleArticleDensity=VLOOKUP(Transports!$E3,ArticleTbl,2,0)
MiddleCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,4,0)
MiddleCompMaxWeight=VLOOKUP(MiddleCompCode,CompartmentTbl,3,0)
MiddleCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,5,0)
MiddleCompSize=VLOOKUP(MiddleCompCode,CompartmentTbl,2,0)
Into cell G3 (Front+Quantity) enter the formula
=IF(OR($C3="",$D3=""),"",MIN(FrontCompSize*FrontCompNum,ROUNDDOWN(FrontCompM
axWeight*FrontCompNum/FrontArticleDensity,-1)))
Into cell H3 (Middle+Quantity) enter the formula
=IF(OR($C3="",$E3=""),"",MIN(MiddleCompSize*MiddleCompNum,ROUNDDOWN(MiddleCo
mpMaxWeight*MiddleCompNum/MiddleArticleDensity,-1)))
Into cell I3 (Back+Quantity) enter the formula
=IF(OR($C3="",$F3=""),"",MIN(BackCompSize*BackCompNum;ROUNDDOWN(BackCompMaxW
eight*BackCompNum/BackArticleDensity,-1)))
Into cell J3 (Total+Quantity) enter the formula
=IF(OR($C3="",SUM($G3:$I3)=0),"",ROUND(SUM($G3:$I3),0))
Into cell K3 (Front+Weight) enter the formula
=IF(OR($C3="",$D3=""),"",G3*FrontArticleDensity)
Into cell L3 (Middle+Weight) enter the formula
=IF(OR($C3="",$E3=""),"",H3*MiddleArticleDensity)
Into cell M3 (Back+Weight) enter the formula
=IF(OR($C3="",$F3=""),"",I3*BackArticleDensity)
Into cell N3 (Total+Weight) enter the formula
=IF(OR($C3="",SUM($K3:$M3)=0),"",ROUND(SUM($K3:$M3),0))
When you created the optional group too, then into range O3:R3 enter the
formulas
=IF(OR($C3="",$D3=""),"",K3/(FrontCompMaxWeight*FrontCompNum))
=IF(OR($C3="",$E3=""),"",L3/(MiddleCompMaxWeight*MiddleCompNum))
=IF(OR($C3="",$F3=""),"",M3/(BackCompMaxWeight*BackCompNum))
=IF(OR($C3="",SUM($K3:$M3)=0),"",N3/(FrontCompMaxWeight*FrontCompNum+MiddleC
ompMaxWeight*MiddleCompNum+BackCompMaxWeight*BackCompNum))
Format cells in row 3 as you need (optional group cells format as
percentage). Copy all formulas and data validations down for some reasonable
amount of rows. Finish the worksheet design (set fonts, borders, column
widths etc.
Start with data entry. Whenever you determine train configuration, and
select articles for all sections, maximal possible quantities (up to section
volume, but not over max allowed weight) and weights of fuel in every
section are calculated.
Arvi Laanemets
