Nov 20 2019 12:49 PM
Hello All:
I have a long formula with many different cell references. Is there a way to make all the different cells references absolute in one shot without F4 every cell reference in the formula?
For example i want all G77 in the formula below to be $G$77
=MIN('LC0-1.4D'!G77,'LC1-1.2D +1.6L'!G77,'LC2-1.2D+1.6L,M2M4M6,LL=0 '!G77,'LC3-1.2D+1.6LM1M3M5M7,LL=0 '!G77,'LC4-1.2D+1.6L M3M6,LL=0 '!G77,'LC5-1.2D+1.6LM1M4M7,LL=0 '!G77,'LC6-1.2D+1.6L M2M5,LL=0 '!G77)
Thanks,
Sam
Nov 20 2019 01:05 PM
Hello @SamFares,
To my knowledge, there is no quicker way than simply using F4 on each cell reference. My suggestion would have been to use find and replace (i.e. find G and replace with $G$) but seeing as though your formula uses cell references beyond the first 26 columns (i.e. LC1, LC2, LL, etc...) this would complicate things. Without creating a macro, I'm afraid my best suggestion is to simply use F4.
Sorry if this isn't the answer you were looking for!
Nov 20 2019 01:42 PM
Define a Name to reference the cell (by default it will be an absolute reference) then use
Apply Names...
on the Name Manager ribbon tab to replace the direct reference by the Name.
Not that I have much experience of doing this, since I ban direct references from my workbooks in the first place.
Nov 20 2019 02:05 PM
SolutionSam, you shall select entire formula to do that.
In cell:
F2 (or double click)->Ctrl+End->Ctrl+Shift+Home->F4
In formula bar
Ctrl+A->F4
Nov 20 2019 02:48 PM
Nov 20 2019 02:50 PM
Sam, you are welcome
Nov 20 2019 03:26 PM
Hi Sergie:
When I do Ctrl+A in the formula bar it doesn't select the entire formula. It doesn't do anything. Am i doing something wrong?
Thanks,
Sam
Nov 21 2019 12:04 AM
For me Ctrl+A works both in the formula bar and the cell.
I realise that my proposed solution was overkill in that I set out to change multiple relative references to a given cell without changing other relative references that may exist within the same formula.
Nov 21 2019 12:42 AM
Sam, perhaps you are not on latest Excel, couple of years ago Ctrl+A didn't work, see for example our discussion here https://techcommunity.microsoft.com/t5/Excel/Editing-text-in-a-cell-Ctrl-A-doesn-t-select-all-ctrl-b.... Not sure when it was introduced.
Anyway, select in formula bar as well by Ctrl+End->Ctrl+Shift+Home or like.
@Peter Bartholomew , you are right, if Ctrl+A works it works in both.
Nov 21 2019 10:12 AM
Jan 21 2021 11:13 AM
I think these answers are over complicating things. I would do a search G77 and replace $G$77 @SamFares
Nov 20 2019 02:05 PM
SolutionSam, you shall select entire formula to do that.
In cell:
F2 (or double click)->Ctrl+End->Ctrl+Shift+Home->F4
In formula bar
Ctrl+A->F4