distance travelled spreadsheet

[kid cisco]

HI there, total novice in excel but am trying to simplify the expenses process for the myself, other management and the directors of my place of work.

We have 35 sites across scotland that we visit regularly, and we get to claim back the mileage travelled on expenses. I have created tables with every possible distance from every possible location to every other location and am looking to create a list of drop down menus that can be selected and then when all locations for that day have been selected the total mileage is calculated at the end of the row. and the total month's mileage calculated at the bottom of the spreadsheet.

question is, how can i achieve this? totally novice at excel, i can do my accounts but thats about it, but having done a bit of digging it looks like it could be a handy tool.

any pointers, tips, suggestions or abuse, greatly appreciated.
TIA

KC

 

[Jonmo1]

Welcome to the board...

Try something like this

Excel Workbook
	A	B	C	D	E	F	G	H
1		City1	City2	City3	City4	City5	City6	City7
2	City1		118	419	289	400	216	172
3	City2	118		417	354	94	366	191
4	City3	419	417		228	244	253	394
5	City4	289	354	228		451	443	54
6	City5	400	94	244	451		477	250
7	City6	216	366	253	443	477		315
8	City7	172	191	394	54	250	315
9
10
11	From	To	distance
12	City1	City4	289
13	City4	City7	54
14	City3	City2	417
15	City6	City5	477
16
17			1237
Sheet1

Hope that helps.

 

[CWatts]

I got beat to the bunch, but here's a slight variation. My Scottish geography is quite poor, so the numbers were made up.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Aberdeen</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dundee</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Edinburgh</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Glasgow</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Paisley</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Aberdeen</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000">-</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">125</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dundee</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000">-</TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center">80</TD><TD style="TEXT-ALIGN: center">90</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Edinburgh</TD><TD style="TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000">-</TD><TD style="TEXT-ALIGN: center">62</TD><TD style="TEXT-ALIGN: center">150</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Glasgow</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">80</TD><TD style="TEXT-ALIGN: center">62</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000">-</TD><TD style="TEXT-ALIGN: center">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Paisley</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: center">90</TD><TD style="TEXT-ALIGN: center">150</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000">-</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD>Start</TD><TD>Leg 1</TD><TD>Leg 2</TD><TD>Leg 3</TD><TD style="TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">Employee 1</TD><TD>Aberdeen</TD><TD>Dundee</TD><TD>Edinburgh</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">110</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">Employee 2</TD><TD>Dundee</TD><TD>Paisley</TD><TD>Dundee</TD><TD>Glasgow</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">90</TD><TD style="TEXT-ALIGN: center">90</TD><TD style="TEXT-ALIGN: center">80</TD><TD style="TEXT-ALIGN: center">260</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">Employee 3</TD><TD>Dundee</TD><TD>Edinburgh</TD><TD>Aberdeen</TD><TD>Dundee</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">185</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C10</TD><TD>=IFERROR(VLOOKUP(B9,$A$1:$F$6,MATCH(C9,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>D10</TD><TD>=IFERROR(VLOOKUP(C9,$A$1:$F$6,MATCH(D9,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>E10</TD><TD>=IFERROR(VLOOKUP(D9,$A$1:$F$6,MATCH(E9,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>F10</TD><TD>=SUM(C10:E10)</TD></TR><TR><TD>C12</TD><TD>=IFERROR(VLOOKUP(B11,$A$1:$F$6,MATCH(C11,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>D12</TD><TD>=IFERROR(VLOOKUP(C11,$A$1:$F$6,MATCH(D11,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>E12</TD><TD>=IFERROR(VLOOKUP(D11,$A$1:$F$6,MATCH(E11,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>F12</TD><TD>=SUM(C12:E12)</TD></TR><TR><TD>C14</TD><TD>=IFERROR(VLOOKUP(B13,$A$1:$F$6,MATCH(C13,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>D14</TD><TD>=IFERROR(VLOOKUP(C13,$A$1:$F$6,MATCH(D13,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>E14</TD><TD>=IFERROR(VLOOKUP(D13,$A$1:$F$6,MATCH(E13,$B$1:$F$1,0)+1,FALSE),"-")</TD></TR><TR><TD>F14</TD><TD>=SUM(C14:E14)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[kid cisco]

ok so i have my massive table of data (mileages) but I am totally stumped as to what to do next. my initial thoughts were to have my data hidden, but have the spreadsheet on the front with the following columns:

day|start|dest1|mileage|dest2|mileage|dest3|mileage|dest4|mileage|dest5|mileage| total mileage

where mileage is the miles travelled between destinations. and the total mileage is obviously the total of all individual mileages. should be simple yet my brain cannot figure it out.