OpenOffice/Excel formula

  Boluwd 15:52 01 Apr 2004
Locked

I am creating a simple monthly hours worked/payment spreadsheet for the wife who teaches aerobics. One column is headed "duration" (Col C) and the next column is "rate per hour" (Col D) and obviously the next column is "payment" (Col E). There are other columns (Date, time etc,) but for the purpose of the query the cells in E = C*D.

In order to avoid typing in the rate per hour every time in each new row, I have one cell say B4 which is the rate per hour and all the cells in column D =$B$4. Easy peasey!! What happens if there is a pay rise (or cut) where the value of B4 changes. How can I put in a formula in Column D which reflects the new rate but does not change all the entries already referencing the old rate. (ie cells D1 to D9 were correctly showing 10 but from D10 onwards the new rate needs to show 12)

I'm actually using Open Office for this but I thought if I mention Excel in the header it would arouse VoG! It's probably less hassle to just enter the rate per hour in each cell of Column D each time but if any of you have some bright ideas I'd be grateful.

  VoG II 15:59 01 Apr 2004

I don't know whether this will work in Open Office.

Select all the cells in Column D with the old rate and Copy. Then Edit/Paste Special and tick Values. This will convert all the =$B$4 to the actual values. Then you can change the rate without changing the already entered values.

  xania 16:03 01 Apr 2004

In Excel there is an easier option. Insert the rate into any cell outside the main spreadsheet. You can then name the cell <Insert><Name>. Now, instead of a constnat, use this name in your formulae. Any change in the value in the constant cell will now automatically update every cell that references it.

  VoG II 16:05 01 Apr 2004

That's what he wants to avoid!

  Boluwd 16:12 01 Apr 2004

Thanks VoG™, this works if I substitue "numbers" for "value" in Open Office's Paste Special menu. You are a genius Sir!

  Sir Radfordin 16:14 01 Apr 2004

VOGs method should work but another approach would be to create a parameters sheet that would allow you to store several hourly rates and then you would just need to change the formular in the cells that you haven't already calcualated values for.

  Boluwd 16:20 01 Apr 2004

Thanks everyone for your contributions. I'm going with VoG™ on this one as it suits the layout of the whole "workbook"...... and it's wifeproof!

Cheers.

  Sir Radfordin 16:22 01 Apr 2004

Don't be silly nothing is wifeproof ;)

  Boluwd 16:26 01 Apr 2004

lol :))

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?