Converting Number to Formulas

  • Thread starter Thread starter John Kiser
  • Start date Start date
J

John Kiser

I have a very long list of numbers that I want to convert
to formulas in their current position. Example:

A B C D
1 16
2 22 4
3 14 (etc.)


I want to convert the value in A1 to the formula =16*$D$2,
A2 to be the formula =22*$D$2
A3 to be the formula =14*$D$2

Since I have so many numbers, rather than do this
manually, I want to see if there is a quick way to do this.

Thanks for your help.
 
in column E

="=" & A1 & "*$D$2"

drag fill it down the column.

Now select these cells and do edit => copy
Select A1 to the last filled (matching) cell and do Edit=>Paste Special and
select Values

no, with these cells still selected, go to the edit menu and choose Replace.
Replace = with =

this will cause the formulas to be evaluated as formulas rather than text
strings.

Now you can delete column E.
 
The other option is to put a formula in any other cell that points to D2, eg say
in A1 put =$D$2. Then Copy cell A1, select all your data and do Edit / Paste
Special / Multiply (Check 'Formulas' in the options as well).

This will put a formula such as for example if you had 22 in a cell originally,
it will now have =22*($D$2) in it. If you don't like the brackets then just do
an edit / replace on ($D$2) with $D$2 and they'll all go.

This allows you to do everything in situ.

Then just delete the stuff in cell A1.
 
Back
Top