Excel 2002: changing link formula from absolute to relative
 

Hi,

I notice that all the formulas link from one work book to another are in
form of "absolute" cell refrence.

Eg. ='[Budget 2010.xls]jh'!$E$222

This does not serve my need as I need to copy the formulas across the
spreasheet.

Usually I would remove the $ manually before copying across.

May I know if there is a way of changing the formula from absolute to
relative, i.e. removing the $ in row only, column only or both automatically?

Thanks

Low

--
A36B58K641

Excel 2002: changing link formula from absolute to relative
 

If you wanted to use Find & Replace, and you have that same formula
repeated in many cells, then highlight the cells, do CTRL-H and use
the following:

Find What: !$E
Replace with: !E
click Replace All.

(removes the $ from the column reference)

Find What: E$
Replace with: E
click Replace All.

(removes the $ from the row reference)

Find What: $
Replace with: (leave blank)
click Replace All.

(removes all $)

Hope this helps.

Pete

On Jul 29, 3:32*pm, Mr. Low wrote:
Hi,

I notice *that all the formulas link from one work book to another are in
form of "absolute" cell refrence.

Eg. ='[Budget 2010.xls]jh'!$E$222

This does not serve my need as I need to copy the formulas across the
spreasheet.

Usually I would remove the $ manually before copying across.

May I know if there is a way of changing the formula from absolute to
relative, i.e. removing the $ in row only, column only or both *automatically?

Thanks

Low

--
A36B58K641

Excel 2002: changing link formula from absolute to relative
 

F4 while you are editing the formula. This will toggle through the 4
options in turn.
--
David Biddulph

"Mr. Low" wrote in message
...
Hi,

I notice that all the formulas link from one work book to another are in
form of "absolute" cell refrence.

Eg. ='[Budget 2010.xls]jh'!$E$222

This does not serve my need as I need to copy the formulas across the
spreasheet.

Usually I would remove the $ manually before copying across.

May I know if there is a way of changing the formula from absolute to
relative, i.e. removing the $ in row only, column only or both
automatically?

Thanks

Low

--
A36B58K641

Excel 2002: changing link formula from absolute to relative
 

It depends on which *direction* you're moving when you create the link.

Going *forward*, from source to target, using say <Copy <PasteSpecial
<PasteLink, will create an *absolute* link.

What you may want to do is use a *backward* link, from target to source.

Click in the target cell,
enter an = sign,
navigate to the source cell,
hit <Enter.

This creates your relative link, which you can easily copy and increment as
needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Mr. Low" wrote in message
...
Hi,

I notice that all the formulas link from one work book to another are in
form of "absolute" cell refrence.

Eg. ='[Budget 2010.xls]jh'!$E$222

This does not serve my need as I need to copy the formulas across the
spreasheet.

Usually I would remove the $ manually before copying across.

May I know if there is a way of changing the formula from absolute to
relative, i.e. removing the $ in row only, column only or both
automatically?

Thanks

Low

--
A36B58K641