Oil collection recording form

[Keef]

I've uploaded an oil collection recording form which I've been playing with for a while.

Although not a legal requirement, I am finding it useful to get an overall picture of what I'm collecting, when and from who which I don't get from the WTNs. It also makes keeping track of WTN season tickets much easier.

I'd welcome suggestions to improve it and I'm quite happy for it to be published on here, maybe alongside the oil usage form or even combine the two into one xls file?

Have a look and a play and see what you think.

http://www.biopowered.co.uk/w/images/b/bc/Oil_Collect_Form_v1.xls

[Julian]

Wow! the colours are a bit bright, Keith!

Looks useful though.  Just wondering how it works with places where you collect only a couple of times a year, it could result in a lot of blank space.

How would it work if you entered the date in place of the numbers down the left hand side and did a year on one sheet?  With sneaky formulas you could still record a monthly total.  I think it's something like SUM IF (....) ,  it's been a while since I went into Excel in depth!

[Keef]

A new "toned down" "year to a sheet" version incorporating Fred's usage form is now on the link in the OP.

Seems there is always going to be a lot of blank space because you need to cover every customer for every day of the year.

I quite like this version although there is more navigating around the page to be had.

[Julian]

What I meant was you only enter the date when you collect oil.  So, for example, between Jan 03 and Jan 14 there were no collections, so it's not necessary to make an entry between these dates.  I'll see if I can find / work out the function to total random dates within a month ( I'm sure it's possible).  That way every row on the sheet will be used giving far less blank space.

[Julian]

It's a bit of a clumsy way of entering the date, but it works ....

http://www.biopowered.co.uk/w/images/4/42/Oil_collection_monthly_total_-_test.xls


Just enter additional days, months and oil quantities in the left-hand columns (doesn't need to be chronological), it'll will work down to row 40 but can obviously be extended.

I'm sure there's a way to do this using dates, but it'll take me quite a while to crack it, I've not seriously played with Excel for years!

[Keef]

Thanks Julian, I can see that working with the litres being in a one dimensional range but can't get my head around how you would do that if the litres are in a two dimensional range (ie. a column for each supplier).

Like you, I haven't used excel in anger for many years and it's straining the grey matter a bit.

I've got a pretty full weekend so it's probably time for a nice glass of wine and try again next week.

[Julian]

Ah, see what you mean ... shame 'cos I just got the date bit working!  It's here ...

http://www.biopowered.co.uk/w/images/a/a6/Oil_collection_monthly_total_-_test_2.xls

I'll have a play with summing ranges (was hoping for a lazy evening ... but I quite enjoy this sort of stuff)

[Keef]

Out of curiosity, what is the purpose of all the $ signs in your formulae?

eg. Febs total is =SUMIF($B$4:$B$40,2,$C$4:$C$40) but if you change it to =SUMIF(B4:B40,2,C4:C40) it seems to function exactly the same.

[Julian]

Simply swiping a range within that formula doesn't seem to work, strange!

This works, but has a really messy formula! ....


http://www.biopowered.co.uk/w/images/e/e4/Oil_collection_monthly_total_-_test_3.xls


The $ signs keeps the reference to the cell constant.  If you put them in, you can copy the selection down a column by dragging and the formula doesn't change.  I'm sure there are better ways of doing it, but once I've found something that works for me I tent not to look too much further ... hence ridiculously long formula like the one on this latest version!

[Keef]

I've had a play and I can't make the formula any better than you so I went for a bit of lateral thinking.

I use my latest version as the base and once I've filled in my collections for the month, I delete the rows for the unused days. Excel corrects all the formulae automatically and you end up with just the data you need.

I'm happy with (in fact prefer) this for my own use and am probably banging my head against a brick wall trying to improve the messy formulae on Julian's idea so the towel is officially thrown in.

I've also made a start on the 'cars' database tables and now I realise the enormity of that task, could do without the oil collection form hanging over me. 

[Tony]

If there is any really repetative editting to get them tabulated maybe try something like Notepad++ which lets you record macros of keystrokes to automate some of the repetative stuff?

[Julian]

Rather lost track of where we are with this!  If we go with Keith's latest version were we going to have a page which links to the spread sheet outside the wiki?  And wasn't there another form we were going to link to?

Anyone with a better memory able to enlighten me to save me trawling through all the posts?

[Keef]

This answers one question

Not a problem - I've enabled XLS uploads.  The security issue with XLS lies safely away from the server, at the user's end

And the other form was a fuel usage form (I think we settled on Fred1's version) which I've put on a tab on the oil collection form as I find it useful to have it a as one file but is easily seperated in you want to.

[Julian]

Thanks Keith,

I'm nursing a sickly Disco back to health ... again!  Once that's done and I've managed to get shot of my last job for the year (was hoping to take December and January off), I'll put a page together.

I'll also try and do something with the cone bottom pictures.  The help offered on the VOD has not been forth coming, so I'll have a stab at a bit of trigonometry, geometry and some diagrams to complete that page.

[Julian]

Ok, I've put up a text page linking to the two spread sheets.  I seemed more logical to separate them, so I did. and I altered Fred1's sheet so it reads "fuel" thus making it applicable to both Bio and veg.

If we get any others offered, they could be added to the same page.