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

[Sandeeep]

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.

[#2]

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:

• Microsoft Support article on changing cell references: https://www.youtube.com/watch?v=L8dsyjTYI9Q
• Excel training article on changing cell references: https://www.youtube.com/watch?v=BnYTwvofM3M
• MrExcel forum post on changing cell references: https://techcommunity.microsoft.com/t5/excel/make-all-cells-references-in-a-long-formula-absolute/td...
• Excel tips and tricks blog post on changing cell references: https://www.youtube.com/watch?v=L8dsyjTYI9Q
  
  

  
  Please click Mark as Best Response & Like if my post helped you to solve your issue.
  This will help others to find the correct solution easily. It also closes the item.

  
  

  If the post was useful in other ways, please consider giving it Like.

  
  

  Kindest regards,

  
  

  Leon Pavesic

[#3]

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.