mutiple cells need $

[blackstar]

Ok so i built several excel files getting data for various people. i then decided to merge them into one file rather than have multiple files everywhere...here's my problem. when i built the originals i forgot to use the "$" for one of the of the references. so now when i try merging all the files into one sheet it doesn't get the info because i've got nothing telling it to only go from say row 1 to 500 for each person.

the point with all this is i'm trying to put together a pivot table.. i tried creating it with the files on seperate tabs but excel won't let me do that.

so is there an easy way to add the $ to several thousand cells or do i have to do them 1 by 1?

[Trebor76]

Hi there,

I wrote this piece of code some time ago that might do the trick:

Sub AnchorReference()

'Anchors all the references (once a range has been selected).

For Each Cell In Selection
SendKeys "{F2}{F4}{Enter}", True
Next Cell

End Sub

Kind regards,

Robert

HTH

[davesexcel]

Hi there,

I wrote this piece of code some time ago that might do the trick:

Sub AnchorReference()

'Anchors all the references (once a range has been selected).

For Each Cell In Selection
SendKeys "{F2}{F4}{Enter}", True
Next Cell

End Sub

HTH

hi Robert,
this is a great code if there is not a formula involved, if you are refererencing one range it would work what if we had a formula

=A1*d3-p8

then ran the code..
what ref becomes absolute??

[Trebor76]

Hi Dave,

Only P8 would become an absolutre reference.

As the user said the problem was to absolute reference external links (not formulas) I thought it would suffice - I could be wrong!!

Cheers,

Robert

[davesexcel]

I don't know exacytly what you are doing but check this out

select all the ranges you need to alter

then
type in the formula

with the correct address being absolute Pressing F4 does that


then hit Ctrl enter

the formulas should work according to their position