Forum Discussion
Make all cells references in a long formula absolute
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
Sam, 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
14 Replies
I think these answers are over complicating things. I would do a search G77 and replace $G$77 SamFares
Sam, 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
SergeiBaklanThanks for your response on this issue. I was struggling with it for quite some time, and felt certain there was a simpler way. I have dozens of these tings and copying them all manually was mind-numbing.
SFwriter , glad it helped
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
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-backspace/m-p/62354. Not sure when it was introduced.
Anyway, select in formula bar as well by Ctrl+End->Ctrl+Shift+Home or like.
PeterBartholomew1 , you are right, if Ctrl+A works it works in both.
- I appreciate it and thank you Sergei!
Sam, you are welcome
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.
- Thank you Peter!
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!
- Thank you PReagan!
