Quick way to Change a Formula to Absolute Reference 2

[jrobin5881]

I know about the use of the F4 shortcut key to convert a formula from relative to absolute after inputting in a cell. However is there a quick way to highlight a column or row and convert all of the formula's to absolutes at once?

 

[GlennUK]

Can you give examples of your formulas, and explain why you want the references in them converted to absolute?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Not quite.

1. Change the first

2. With the first cell selected, Double-click the little square box in the lower right-hand corner of the selection border.

VOLA!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If it is just to automate pressing F4 key, use macro, select range and run it:

Sub ChangeRefStyle()
Dim c As Range
For Each c In Selection
    Application.SendKeys "{F2}{F4}{ENTER}"
Next c
End Sub

combo

 

[GlennUK]

This is probably safer:

Sub ChangeRefStyle()
Dim c As Range
For Each c In Selection
    c.Formula = Application.ConvertFormula(c.Formula, xlA1, , True)
Next c
End Sub

... note: this hasn't been coded to cope with array formulae.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

-> is there a quick way to highlight a column or row and convert all of the formula's to absolutes at once?

Any time you have a range selected, you can enter whatever is in the active cell into all cells in the range by pressing [Ctrl]+[Enter]. So you could select the range, edit your formula with F4, then press [Ctrl]+[Enter].

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hey Hot Shot, John Boy! Lernd me sumthin' t'day!! ==> *

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

whoa! I taught Skip something?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

To paraphrase Yakov Smirnoff, "I love this Tek-Tips!"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Ok, who's Yakov Smirnoff?


That's something I read somewhere (what higgins said), but totally forgot about it until I read it again here! Neato!

So, jrobin5881,

Did it work yet for you? I s'ppose that's the question.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Google Yakov, son. Maybe he was before your time.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Okay, I found 'im. I see the usage comparison now.
I think I may have seen him once in a commercial or movie or something - don't remember. But it wasn't "his show" or anything.

--

"If to err is human, then I must be some kind of human!" -Me

 

[JPaules]

I use ASAP Utilities and there is a function in there to chage relative formulas to absolute...and vice versa. Very easy and I don't have to use any VBA.

--
JP

 

[GlennUK]

Hi JP,

oh yes, ASAP Utilities, very good. Have used them in the past ... excellent download. I'd forgotten about how useful some of those utilties are.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[JPaules]

I have found so many helpful features in that add-in! Sure, a lot of the command can be down in Excel with formulas, etc but I don't have to remember so many functions with ASAP.

I especially like the text commands. My favorite is the one that allows me to add a string of text before and/or after the value of a cell. I use that quite often. I know I could concatenate but I find it simpler to do it all in one step.

--
JP