How to apply formula to a range of cells that already have value in them?

[yichuansancun]

Hi -

I have a spread sheet in which each cell already has a value. The values are numbers. After inserting a new column with numbers, I need to apply formula to each cell so that each cell's value will the existing value times the corresponding number in the new column. Is there a way I can do this in a more efficient way instead of going into each cell manually type "=" and "*" formula? Past special won't work because I need the formula to be in the each cell.

Thanks in advance.

Perri

 

[Boller]

Use a third column containing =A1*B1

 

[yichuansancun]

Thanks, but it is a huge spreadsheet, if I use a third column for each existing column, it will be very messy

 

[CWatts]

I'd think the easiest way via formula would be to make a new sheet and use a function like:

=OriginalSheet!$B2*OriginalSheet!C2

and copy and paste as necessary. The only other option would be to use some sort of vba and go cell by cell taking the existing formula, putting it in parenthases, and adding *$B2 afterward (where 2 is the variable row number).

 

[yichuansancun]

works

Thanks!

 

[Boller]

Thanks, but it is a huge spreadsheet, if I use a third column for each existing column, it will be very messy

You could keep the columns hidden that contain the existing numbers.

It sounds to me that a VBA solution would probably be better in that it could avoid inserting new columns and formulas, copying/pasting, etc.