Forum Discussion

Sandeeep's avatar
Sandeeep
Brass Contributor
Sep 20, 2023

Formula | Change all cell references in one go | Shortcut key

Formula | Change all cell references in one go | Shortcut key

 

So I have the below formula.

What it does:

  1. It gets the value from (Header) AH1.
  2. Check if that value is seen in Y2
  3. If it is, get the substring
    1. from || to ||,
    2. or if its at the start then, start to ||,
    3. or if its at the end, || to end.
  4. As the input in Y2 would be like: Name=John||Birth=Jan||year=2022||place=London
    1. Where the Name, Birth, year, and place may not be in the same order.

 

Formula:

=IF(ISERROR(FIND($AH$1,Y2)),"",IF(ISERROR((MID(Y2,FIND($AH$1,Y2)+LEN($AH$1),FIND("||",Y2,FIND($AH$1,Y2))-FIND($AH$1,Y2)-LEN($AH$1)))),(MID(Y2,FIND($AH$1,Y2)+LEN($AH$1),LEN(Y2))),(MID(Y2,FIND($AH$1,Y2)+LEN($AH$1),FIND("||",Y2,FIND($AH$1,Y2))-FIND($AH$1,Y2)-LEN($AH$1)))))

 

Help Needed:

So I need to change all Y2 to say [@[Information]] and all $AH$1 to Table1[[#Headers],[Name=]]

 

What is the shortcut key to select all the similar references.

I.e select so I can change all the occurrences of $AH$1 to Table1[[#Headers],[Name=]]

i.e Just like I've highlighted below, but highlight all $AH$1 to click and change all at the same time.

 

 

Please don't say find & replace: I need to change this often, for multiple columns each with different values to change.

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    Hi Sandeeep,

    There is no shortcut key to select all similar references in Excel. However, there are a few workarounds that you can use.

    Another workaround is to use the Go To dialog box. To do this, follow these steps:

    1. Press Ctrl+G to open the Go To dialog box.
    2. In the Reference box, enter the reference that you want to change.
    3. Click the Special button.
    4. In the Go To Special dialog box, select the Cell references option.
    5. Click the OK button.

    This will select all of the cells that contain the specified reference. You can then change all of the references at once by typing the new reference in the formula bar and pressing Enter.



    Finally, you can also use a VBA macro to change all of the references in a worksheet. To do this, you can use the following code:

     

    Sub ChangeReferences()
    
        'Declare variables
        Dim ws As Worksheet
        Dim cell As Range
        Dim oldReference As String
        Dim newReference As String
    
        'Set the worksheet
        Set ws = ActiveSheet
    
        'Get the old reference
        oldReference = "$AH$1"
    
        'Get the new reference
        newReference = "Table1[[#Headers],[Name=]]"
    
        'Loop through all of the cells in the worksheet
        For Each cell In ws.UsedRange
    
            'If the cell contains the old reference, replace it with the new reference
            If cell.Value Like oldReference Then
                cell.Value = Replace(cell.Value, oldReference, newReference)
            End If
    
        Next cell
    
    End Sub

     

     

    To use this macro, simply copy and paste it into a new module in the Visual Basic Editor (VBE). To open the VBE, press Alt+F11.

    Once you have pasted the macro into the VBE, you can run it by pressing F5.

    You can also check these links as a reference:

    • Sandeeep's avatar
      Sandeeep
      Brass Contributor
      Thanks, unfortunately, I will be trying to do this on a Screen share on other people's computers, and this would just take too long.

      I already have a UDF I made and several macros. But the usage I needed this for was when helping others via screen share. They would not have all my UDFs and VBA scripts.

      Also, it needs to be a formula as it is accessed via SharePoint online.
      I will try the GoTo method, and mark it as the Best reply if it is the best solution for me.

Resources