Any Excel experts here ?

[Mike P the 1st]

This is probably not hard to answer for some.

I have a mileage log on Excel and I would like the closing daily mileage odeometer cell, to auto copy the figure to the next days starting mileage cell when I type it in.

Cheers

Mike P the 1st

[Andy Burns]

Unless I've misunderstood, then that's the easiest formula possible

assuming you already have something like

A B C D

1 date start end distance 2 1/jan 1000 1010 10 3 2/jan 1010 1030 20 4

where e.g. D2=C2-B2, so it calculates the distance column for you

you just need B3=C2, then drag B3 (by the corner) all the way down the B column, so it alters the references to B4=C3, B5=C4, B6=C5 etc

[Malcolm]

'(starting cell)' contains '=(closing cell)' Propogate this and you will find that the (starting cell) will initially contain the (closing cell) number all of the way down, but this will change each time you put in a new value

Malcolm

[stuart noble]

If "start" always equals the previous day's "end", you could probably do away with one of those mileage columns altogether, and not have to autocopy anything. E.g. D3 could equal B3-B2, and column C wouldn't then be required. A cell somewhere containing the formula =sum(d:d) would give you a running total, which might be useful.

[Andy Burns]

But with the two columns you can override the start formula with a number e.g. if there's been a private journey since the last claimable one (assuming this is expenses related).

[Spamlet]

"Mike P the 1st" wrote in message news: [e-mail]...

I may have misinterpreted, but all I would do, is hit '=' in the square you want to copy to, and then simply click the square with the previous day's total in, and press enter. Done. You might want to link it to other squares for further calculations - fuel consumption/vat/pence per mile etc etc too. Jut clicking the square saves an awful lot of rewriting of formulae.

What I've been waiting for is a gizmo that adds postcode distance calculators to spreadsheets so that you don't even have to calculate the mileage yourself. Strangely, this facility is a long time coming, but I'm sure it will in the end: nearest at the mo is to link to an online calculator, but that's too complicated for me.

It does not matter if the next day's cell is in another worksheet or even another workbook, Excel will create the hyperlink for you. (There may be hundreds of such links in spreadsheets - which gets a bit scary at times to me of little brain! Especially if you are embedding and linking in Access, Word etc as well! Argh: and you can link to online things too!)

S

[Mike P the 1st]

On Sat, 04 Sep 2010 17:10:52 +0100, Mike P the 1st gently dipped his quill in the best Quink that money could buy:

What a brilliant NG this is ! Thanks to all the replies and yes it is for tax purposes.

Mike P the 1st

[stuart noble]

Yes, I did wonder about that. Plenty of templates out there if you need something ready made. A search for "Excel mileage calculator" brings up a few.