Fixing cells ($) for multiple cells

[sputnik17]

I know I can put a $ sign for all references in a formula for a cell by pressing F4, but is there a quick way for doing this for multiple cells (e.g. a column) or do I have to go through all the cells individually?

e.g.
=A1
=A2
=A3

to

=$A$1
=$A$2
=$A$3

Thanks

 

[VoG]

ASAP can do this http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=102&utilities=97&lang=en_us

 

[sputnik17]

Thanks VoG

It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

Cheers

 

[goliath624]

You can use Edit-> Replace to a limited extent.

Just select the range of cells, do something like Replace "A" with "$A$".

 

[Jonmo1]

Thanks VoG

It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

Cheers

yes you can, look at the convertformula function...

Here's a simple example

Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, xlA1, xlA1, 1)

The 4th argument is the absolute style

1 = col & row abs
2 = row abs, col relative
3 = col relative, row abs
4 = row & col relative

Unfortunately, it converts the ENTIRE formula's references. so you can't mix it up like

=$A$1+B1

you couldn't have it convert to that, both cell refs would have the same absolute style..