Locking Formulas

  • Thread starter Thread starter tanner.robin
  • Start date Start date
T

tanner.robin

Is there a way to lock a series of selected formulas without going
through and editing every individual one? This is very time
consuming. I have looked and I haven't found anything thus far.

Thanks.
 
What do you mean by lock and why do you have to
edit a formula to lock it?

You can select the whole spreadsheet, do format>cells>protection and clear
locked, then select all the formulas you want to lock and reverse that and
select locked, then protect the worksheet


--


Regards,


Peo Sjoblom
 
What do you mean by lock and why do you have to
edit a formula to lock it?

You can select the whole spreadsheet, do format>cells>protection and clear
locked, then select all the formulas you want to lock and reverse that and
select locked, then protect the worksheet

--

Regards,

Peo Sjoblom
Click to expand...

I'm referring to locking the formula so I can drag it without the
values in the formula changing..You know how you can lock a formula
with a $ sign? I want to add dollar signs to a series of selected
formulas. They all have different variables and refer to different
things. I need to lock them all though so I can select them and drag
the formulas to other cells (to autopopulate it with that same
formula). I find myself doing this quite often and sometimes it is
upwards of 10 to 30 formulas. Below is an example of the formula
locking technique I'm talking about.

=IF(CELL("contents",E517)=C536,"*",E517)

=IF(CELL("contents",$E$517)=$C$536,"*",$E$517)

=IF(COUNTIFS(I17:I500,">00:05:00",I17:I500,"<=00:30:00")=0,"",COUNTIFS(I17:I500,">00:05:00",I17:I500,"<=00:30:00"))

=IF(COUNTIFS($I$17:$I$500,">00:05:00",$I$17:$I
$500,"<=00:30:00")=0,"",COUNTIFS($I$17:$I$500,">00:05:00",$I$17:$I
$500,"<=00:30:00"))

I have a tone of formulas that I want to do this with. The purpose of
doing this is to be able to drag the formula and auto populate other
cells. So I can make a dashboard.
 
I'm referring to locking the formula so I can drag it without the
values in the formula changing..You know how you can lock a formula
with a $ sign? I want to add dollar signs to a series of selected
formulas. They all have different variables and refer to different
things. I need to lock them all though so I can select them and drag
the formulas to other cells (to autopopulate it with that same
formula). I find myself doing this quite often and sometimes it is
upwards of 10 to 30 formulas. Below is an example of the formula
locking technique I'm talking about.

=IF(CELL("contents",E517)=C536,"*",E517)

=IF(CELL("contents",$E$517)=$C$536,"*",$E$517)

=IF(COUNTIFS(I17:I500,">00:05:00",I17:I500,"<=00:30:00")=0,"",COUNTIFS(I17:I500,">00:05:00",I17:I500,"<=00:30:00"))

=IF(COUNTIFS($I$17:$I$500,">00:05:00",$I$17:$I
$500,"<=00:30:00")=0,"",COUNTIFS($I$17:$I$500,">00:05:00",$I$17:$I
$500,"<=00:30:00"))

I have a tone of formulas that I want to do this with. The purpose of
doing this is to be able to drag the formula and auto populate other
cells. So I can make a dashboard.
Click to expand...

I meant ton*. I hate grammatical errors.
 
You would need VBA for that

Press Alt + F11, click insert>module and paste the
following

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub

press Alt + Q to close VBE and select the range with formulas and press Alt+
F8 to run the macro

Beware of any line wrapping


--


Regards,


Peo Sjoblom
 
That has to be on the same line (I told you to beware of line wrapping)

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,xlAbsolute)


--


Regards,


Peo Sjoblom
 
How ya doing Harold?

BTW..........quoting a Hawley solution could be considered sacrilege<g>


Gord
 
Hi Gord!

Lol, yes the injoke was somewhat deliberate. Still, he does have a pretty
good solution on this.
(Occational readers, you don't want to hear this story, believe me)

I'm fine thank you. Last year was mostly spent on SharePoint technology (btw
good material that), this year it's too many Oracle databases and
SQLservers. But at times there's a fun Excel task to do, which I guess is
keping my sanity. I'm also beginning to get the grips on Excel12, so I hope
to spend some time here again. And it's summer and Oslo is baking. Life is
great at the moment.

I hope you and our common friends are all well.

Best wishes Harald
 
All well around here.

Will be good to have you spending some more time in these groups.

We have missed your dry wit.


Gord
 
Back
Top