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

ID: Q70096

The information in this article applies to:

Microsoft Excel for Windows, versions 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, click the article number below to view the article in the Microsoft Knowledge Base:

Q116028 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

Additional query words: 3.0 3.00 4.0 4.00 sort

Keywords :
Version : WINDOWS:3.x,4.x,5.0,5.0c,7.0
Platform : WINDOWS
Issue type : kbhowto