Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Absolute References (xl 97)

    Does any one know how to turn a whole sheet en mass to absolute call values, rather than relative values.
    I have a spreadsheet that links in to approx 1000 rows of data in another spreadsheet.
    I then need to link in to 10 other spreadsheets, each time linking to the same cell references on those 10 spreadsheets.
    The problems is, after I've linked in the first 1000 rows to the first external spreadsheet, I then need to copy these down so they are underneath, i.e. they'll end up being rows 1001 to 2000.
    By doing this though, as the links are relative references, the formulas change to look at unwanted cells in the external spreadsheets.

    Pressing F4 for each cell in the 1000 rows is not really an option...
    Any help would be much appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute References (xl 97)

    Like this:
    <pre>Sub ConvertToAbslute()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlAbsolute)
    Next
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •