SOLVED

Contributor

# 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

12 Replies

# Re: Make all cells references in a long formula absolute

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!

# Re: Make all cells references in a long formula absolute

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.

best response confirmed by SamFares (Contributor)
Solution

# Re: Make all cells references in a long formula absolute

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

# Re: Make all cells references in a long formula absolute

Thank you PReagan!

Thank you Peter!

# Re: Make all cells references in a long formula absolute

I appreciate it and thank you Sergei!

# Re: Make all cells references in a long formula absolute

Sam, you are welcome

# Re: Make all cells references in a long formula absolute

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

# Re: Make all cells references in a long formula absolute

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.

# Re: Make all cells references in a long formula absolute

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.

# Re: Make all cells references in a long formula absolute

Yes you are correct. I have Excel 2016 which doesn't do it.

Thanks!

Sam

# Re: Make all cells references in a long formula absolute

I think these answers are over complicating things.  I would do a search G77 and replace \$G\$77 @SamFares