Absolute References

[The_Game]

I'm having a difficult time working with absolutes when referencing other sheets. I want Workbook1 to reference a sheet in Workbook2, but it needs to be an absolute reference because there are filters that throw everything around and Workbook1 isn't tracking those changes. So if I can absolute all of A1 to N105 quickly, that'd be great. I want to reference $A$1 and then drag that across and down to save time, because the alternative is manually referencing hundreds of cells ($B$1, $C$1, etc). How can I drag this absolute around without removing both "$"?

Thank you so much!!!!

 

[Fishboy]

I'm having a difficult time working with absolutes when referencing other sheets. I want Workbook1 to reference a sheet in Workbook2, but it needs to be an absolute reference because there are filters that throw everything around and Workbook1 isn't tracking those changes. So if I can absolute all of A1 to N105 quickly, that'd be great. I want to reference $A$1 and then drag that across and down to save time, because the alternative is manually referencing hundreds of cells ($B$1, $C$1, etc). How can I drag this absolute around without removing both "$"?

Thank you so much!!!!

Hi The_Game,

The easiest way to do this would be to save a COPY of your workbook as a backup, next remove the absolute reference $ signs, drag-fill across and / or down as far as required. Once you have done that, try out the following macro written by one of the Admins on OzGrid. Select the area you want to apply absolute references to, run the macro and select option 3:

Sub MakeAbsoluteorRelative()
'Written by OzGrid Business Applications
'www.ozgrid.com


Dim RdoRange As Range
Dim i As Integer
Dim Reply As String
 
'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
 & "Relative row/Absolute column = 1" & Chr(13) _
 & "Absolute row/Relative column = 2" & Chr(13) _
 & "Absolute all = 3" & Chr(13) _
 & "Relative all = 4", "OzGrid Business Applications")
 
   'They cancelled
   If Reply = "" Then Exit Sub
   
    On Error Resume Next
    'Set Range variable to formula cells only
    Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
 
        'determine the change type
    Select Case Reply
     Case 1 'Relative row/Absolute column
        
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
        Next i
       
     Case 2 'Absolute row/Relative column
        
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
        Next i
       
     Case 3 'Absolute all
        
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
        Next i
       
      Case 4 'Relative all
        
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
        Next i
         
       
     Case Else 'Typo
        MsgBox "Change type not recognised!", vbCritical, _
        "OzGrid Business Applications"
 End Select
 
    'Clear memory
    Set RdoRange = Nothing
End Sub

 

[The_Game]

You sir are amazing! Thank you for your help!!

 

[Fishboy]

You sir are amazing! Thank you for your help!!

Credit for the code itself goes to the Admins over at OzGrid, but as far as me finding the code for you goes, you are most welcome