Excel Formulas

April 1, 2009 at 11:02:33
Specs: Windows XP
I would like to apply a formula to an entire column on constants, yet keep the formula in place and not have to create a new column.

A1 = 0.5
A2 : A10 Have constants
A2 = 100
A3 = 200
A3 = 300 etc...

Current formula in A2 is 100*$A$1 = 50

How do I copy this formula to the entire column, and keep formula active so any changes to A1 are applied to the entire column.

If I use copy A1 and paste special multiply, the value changes to the proper number, but the formula does not remain active.
I can not copy and paste, because the constant value of A2 carries to all remaining cells.

Please help.


See More: Excel Formulas

Report •

April 1, 2009 at 12:19:58
If the constants you give in your example are your real constants, then put this in A2 and drag it down:


ROW()*100)-100 will return the "constants" you listed in your example. You won't see them as constants anymore, but the results will be the same.

If those are just made up constants, then use this code to put the formulae in the cells. Modify the LastRow = 4 line to match the Row that holds your last constant.

Sub ConstForm()
'Set this variable equal to the Row that holds your last constant
   LastRow = 4
'Loop from A2 to LastRow
   For rw = 2 To LastRow
'Build formula using constant in each cell
    Range("A" & rw).Formula = "=" & Range("A" & rw) & "*$A$1"

Report •

April 1, 2009 at 12:45:40
Thanks, not sure I did it correct.
The constants happen to be current "list prices" and may change in the future, so I would like to keep them as their existing numbers if possible.
A1 = 0.50

A2 = $1293 formula = 1293*$A$1
A3 = $1344 formula = 1344*$A$1
A4 = $1402 etc...

Result is cell
A2 ends up displaying as $646.50
A3 ends up displaying as $672.00 etc...

Where both the Mult in A1 and the list price in A2 (rarely) can be changed at some point in the future. Obviously the multiplier is the more flexible of the 2.

I need to be able to copy just the $A$1 to the column of cells that already contains a number.


Report •

April 1, 2009 at 13:19:58
re: I need to be able to copy just the $A$1 to the column of cells that already contains a number.

No, you need to add an = sign, the * operator and the reference to $A$1. You can't do that with a copy/paste or a drag and fill.

The macro I offered will do just what you want - retain the constants in A2 through the last row, while adding the rest of the characters needed to turn the contents of the cell into the formulae you are looking for.

The formula in A2 will end up looking like:

=1293*$A$1 and will display 646.5, formatted however you like.

It would have no impact on A1 - you can change that at any time.

In the future, if you wanted to change the constants in A2 and beyond, you would simply edit the formula in each cell - or reenter all of the constants and run the macro again.

Bottom line is you can't have a constant in a cell and also have a formula. You can have a formula that contains a constant, but it's still a formula.

Why not just hide the column with the constants and use a "regular" formula? That sure would make future editing the constants a whole lot easier.

Report •

Related Solutions

Ask Question