Sep 20 2023 04:58 AM
Sep 20 2023 04:58 AM
Formula | Change all cell references in one go | Shortcut key
So I have the below formula.
What it does:
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.
Sep 20 2023 05:48 AM
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:
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:
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.
Sep 20 2023 06:13 AM