How do I keep one cell value constant in copy down feature?

[JimTrail]

Hello;

I have got a column with price quotes. I have got one cell with the sales tax rate.

I want to use the copy down feature to calculate the sales tax for each of the cells in the column.

Right now, the sales tax rate is in spreadsheet cell D11 and the column with the amounts that I want to calculate the sales tax for are in cells F11 through F14.

If I have calculate the amount including sales tax in cell H11 like this D11*F11 it will calculate the proper amount of sales tax into cell H11.

However, if I use the copy down feature from cell H11 for cells H12 through H14 the formulas will be D12*F12, D13*F13, and so forth.

Cells D12 and D13 are empty cells. I need to hold the sales tax rate in cell D11 constant in each of the calculations copied down. I need the cell formulas to be like this: D11*F12, D11*F13, D11*F14.

I know in Lotus 1-2-3 you can enclose the cell reference for the constant cell in a dollar sign like this, $D11$*F11, and then copy down. Lotus would keep the value D11 in the copy down cells.

However, OpenOffice Calc won't do that.

Does anyone have the solution to my problem?

Thanks.

[jrkrideau]

I need to hold the sales tax rate in cell D11 constant in each of the calculations copied down. I need the cell formulas to be like this: D11*F12, D11*F13, D11*F14.

I know in Lotus 1-2-3 you can enclose the cell reference for the constant cell in a dollar sign like this, $D11$*F11, and then copy down. Lotus would keep the value D11 in the copy down cells.

However, OpenOffice Calc won't do that.

=$B$1 works fine for me. Looks like a minor format difference between the two spreadsheets.

An alternative, and IMHO, better, approach is to name D11 and use the named range in the equations. This provides a much better audit trail if you are making changes later or doing some debugging.

[JimTrail]

jrkrideau;

Thanks for responding to my question.

On second thought I think that the way Lotus works is with enclosing the column letter in the dollar sign like your example $B$1. I have not used Lotus in a year or so and forgot the exact format.

The last part of your post - naming D11 in the named ranges - how do I do that?

Thanks.

[crusader]

I need to hold the sales tax rate in cell D11 constant in each of the calculations copied down.

Enter =F11*$D$11 in cell H11 and copy it down as needed.

[jrkrideau]

jrkrideau;

Thanks for responding to my question.

On second thought I think that the way Lotus works is with enclosing the column letter in the dollar sign like your example $B$1. I have not used Lotus in a year or so and forgot the exact format.

The last part of your post - naming D11 in the named ranges - how do I do that?

Thanks.

It's been 20+ years since I used Lotus123 so my memory of the exact format is a bit vague too.

Re naming D11
Insert > Names > Define > supply name
To use
Insert > Names > Insert

Overall much nicer than trying to remember what some blasted cell like D11 is supposed to be doing in a formula like D2^2^.5*D11/2 [1]

1. I take no responsibely for evaluating this.