Forum Discussion
Sandeeep
Sep 20, 2023Brass Contributor
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:
- It gets the value from (Header) AH1.
- Check if that value is seen in Y2
- If it is, get the substring
- from || to ||,
- or if its at the start then, start to ||,
- or if its at the end, || to end.
- As the input in Y2 would be like: Name=John||Birth=Jan||year=2022||place=London
- 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.
- LeonPavesicSilver 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:
- Press Ctrl+G to open the Go To dialog box.
- In the Reference box, enter the reference that you want to change.
- Click the Special button.
- In the Go To Special dialog box, select the Cell references option.
- 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-p/1022811
- 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
- SandeeepBrass ContributorThanks, 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.