Hi all,

I was helped out earlier on the General Discussion forum so I thought I'd upload my greatest excel achievment!

At work we had a simple calculator to work out the cost of a product over a date range and calculate it acurately on a per day basis. I wanted to improve upon this so that only a from and to date had to be entered and I have done, plus it calculates many products at once.

There are also macros now to paste lines into another workbook which can work out the next empty row. Using a counter wasn't an option as some lines would be added manually.

There are obvious limitations with this due to IF. Also if the price of a product chages during the period you are calculating so have to use the Original sheet. If anyone can make my meager contribution more ellagant then please feel free to assimilate and regurgetate! I've really go to work on the presentation of this as well.

Most of the formulas are hidden below and to the right of the visable sheets, but there are other hidden sheets you may want to look at. The main formulas are;

To work out start month for use in CONCATENATE:
=IF(C10<"",A10,IF(C9<"",A9,IF(C8<"",A8,IF(C7<" ",A7,IF(C6<"",A6,IF(C5<"",A5,IF(C4<"",A4))) ))))

To work out first broken period rental:
=ROUND(IF(A4=Original!I4,C4/Original!L4*(Original!J4+1-A4),IF(A4=Original!I5,C4/Original!L5*(Original!J5+1-A4),IF(A4=Original!I6,C4/Original!L6*(Original!J6+1-A4),IF(A4=Original!I7,C4/Original!L7*(Original!J7+1-A4),IF(A4=Original!I8,C4/Original!L8*(Original!J8+1-A4),IF(A4=Original!I9,C4/Original!L9*(Original!J9+1-A4),IF(A4=Original!I10,C4/Original!L10*(Original!J10+1-A4),""))))))),2)

To work our whole month rentals:
=IF(A4=Original!I6,Original!M5,IF(A4=Original!I7 ,Original!M6,IF(A4=Original!I8,Original!M7,IF(A4 =Original!I9,Original!M8,IF(A4=Original!I10,Origi nal!M9,IF(A4=Original!I11,Original!M10,IF(A4=Ori ginal!I12,Original!M11)))))))
=IF(B4Original!J6,Original!M5,IF(B4Original!J7,O riginal!M6,IF(B4Original!J8,Original!M7,IF(B4Ori ginal!J9,Original!M8,IF(B4Original!J10,Original!M 9,IF(B4Original!J11,Original!M10,IF(B4Original!J 12,Original!M11,8)))))))
=C4*(E42-E43)
C4 is the cost and E42+43 are the values returned from the above


After all that I can't upload the rental calculator, only room for the bulk calculator. Oh well the formula I used to get the next free row was:

Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Right, I've managed to upload the rental clac by deleting masses of stuff out of the workbook. 39K is a bit tight even for a zip ExcelBanter.com.

Just asked for more advice on this forum so I thought I'd upload the latest version of my rental calculator. Well to be honest this is not my creation but a colleagues of which I have taken over development.

I hope this is usefull to others.