Using FORMULA.CONVERT() to Change Relative/Absolute References

Last reviewed: August 23, 1996
Article ID: Q70096

The information in this article applies to:

Microsoft Excel for Windows, version 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

You can use the FORMULA function with the FORMULA.CONVERT function to change every reference in a selected area from relative to absolute reference. You can do this by using the FORMULA.CONVERT function to calculate the first argument, formula_text, of the FORMULA function.

However, because the FORMULA function will fail if you attempt to use A1-style references, you need to make sure that the FORMULA.CONVERT function is returning an R1C1-style formula and not an A1-style formula.

MORE INFORMATION

The third argument of the FORMULA.CONVERT function, To_a1, determines whether an A1-Style or R1C1-style reference is returned.

Example

The following macro is a short example that will convert all the references in the currently selected area into absolute references.

NOTE: This macro assumes that the references in the selected area are currently in A1-style. If you want to select an area that contains relative references in R1C1-style, change the second argument of the FORMULA.CONVERT function from TRUE to FALSE.

   A1: =FOR.CELL("curcell",,TRUE)
   A2: =FORMULA(FORMULA.CONVERT(GET.CELL
        (6,curcell),TRUE,FALSE,1,curcell),curcell)
   A3: =NEXT()
   A4: =RETURN()

The FOR.CELL function starts a For.Cell-Next loop that will loop through the currently selected area. "curcell" is just the ref_name given to the new cell through each loop.

The second line converts each reference in the selected range to an absolute references in R1C1-style, and then places it back into the current cell of the selection. If you wanted the macro to change absolute references to relative references, you would replace the fourth argument of the FORMULA.CONVERT function from 1 to 4. You must also include curcell as noted above otherwise, it will convert references based directly on the macro sheet rather than the worksheet cells.

NOTE: The third argument of the FORMULA.CONVERT function must be FALSE to use the FORMULA.CONVERT function to return the formula_text argument to the FORMULA function. If this third argument is TRUE, or if the formula_text argument uses A1 style references and this third argument is omitted, you will receive a macro error.

For an example of how to perform this function using a Visual Basic for Applications macro, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q116028
   TITLE     : XL: VB Macro to Change Between Relative/Absolute References

REFERENCES

"Function Reference," version 4.0, pages 168-171

"Microsoft Excel Function Reference," version 3.0, pages 89-91

KBCategory: kbprg kbcode
KBSubcategory:
Additional reference words: 7.00 3.0 3.00 4.0 4.00 5.00 sort

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 23, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.