Lock a Group of formulas

[PhilMyWallet]

I have a column of formulas (all different) which reference a seperate worksheet. I need to copy all the formulas in the column and paste them into a seperate column but keeping the same referenced cells.

I understand I can lock the formulas individually, however there are 432 formulas to lock.

Is there any way I can lock the lot in one go?


Regards



Phil

 

[PhilMyWallet]

This is for Excel. Quite important factor missed.

 

[Loomah]

Do you need to keep the original formulas? If not cutting rather than copying will retain the reference to the precedent cells.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PhilMyWallet]

if I copy & paste the formula (Sum(C1:C2)) from column a to column b then the formula changes to Sum(D12).

I need the formula to show (Sum(C1:C2)) after it has been copied, however I need to do this for over 400 formulas (in one column), so I need to lock all the formulas before copying them. Can I lock them all at once?

Hope this makes sense.


Phil

 

[Loomah]

I repeat my question
Do you need to keep your formulas in column A after copying them to column B?

If you don't then you could CUT the formulas and PASTE them rather than using COPY.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PhilMyWallet]

I need to copy them, however even if I cut them I would still be in the same situation, as it would still move the reference on by one cell to the right.

Any other thoughts

 

[stubnski]

PhilMyWallet,
use $ signs in front of column letters and row numbers to lock the formula to specific cells, so (SUM($C$1:$C$2))


Stubnski

 

[Loomah]

even if I cut them I would still be in the same situation, as it would still move the reference on by one cell to the right

Um, no it wouldn't. Unless Excel 10, 11 & the upcoming 12 are drastically different!

Anyway here's a possible solution to your dilema. This doesn't "lock" the formulas but will effectively copy the same formulas to a column 5 columns over.

Sub standard()
Dim c As Range
For Each c In Range("f1:f30")'change to your range
    c.Offset(0, 5) = c.Formula 'change num of cols over
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PhilMyWallet]

I can't beleive I have been using excel for 10 years & I didn't know that cutting and pasting would keep the formulas static.

Now I know this I have found an alternitive solution :

Copy the full column into a sperate sheet on the same workbook making sure I copy into the same column on the new worksheet.

Cut the column and paste into the column I required (keeping it in the new worksheet).

Copy & paste into the working worksheet. (then delete new worksheet)

Bingo !!!!!

Thank you all for your help !!!!

 

[dcompto]

if I copy & paste the formula (Sum(C1:C2)) from column a to column b then the formula changes to Sum(D12).

I need the formula to show (Sum(C1:C2)) after it has been copied, however I need to do this for over 400 formulas (in one column), so I need to lock all the formulas before copying them. Can I lock them all at once?

You can use Find and Replace on Column A, using the following criteria and Replace All:

then copy and paste to Column B.

 

[PhilMyWallet]

Thought of that one too. As my references were over several columns on each formula, it would have taken me just as long to do it that way.

The cut and paste solution worked ok though.


Thanks anyway


Phil