How to avoid formula circular references and hiding and locking the formula

Dear Friends & Experts,

If we wanted to hide the formulas and lock the particulars cells ( contained with formula ) how can we do that ?

Further, how can we avoid the circular reference permanently if an excel sheet contains more formulas , as the same effects to the accurate result .

Herewith attached the excel sheet for your ready reference. Here I wanted make the salary structure accurately by using formulas as per the company policy and wanted to hide and lock the formulas to avoiding editing.

Your kind reply in this regard would be highly appreciated.

Thanks

To lock cells and hide formula, you need to select the cell(s) and choose Format | Format cells ... | Protection Tab | tick locked and tick hidden.

Once all the cells are locked and/or hidden, you then need to Protect the sheet with a password: Format | Protect sheet ... | choose the features you want protected and input a password.

The circular reference is because cell F11 has the formula: =ROUND(F17*F4,0) but cell F17 has the formula: =SUM(F11:F16)


Regards, TMS

Hi Venkat,

To hide & protect the formulas, format those formula cells as locked & Hidden and protect the sheet.
For circular reference issue, see Iterative calculation under formula tab in Excel options. thanks.

Regards,
DILIPandey
<click on below * if this helps>

In excel 2007, you can go to office button > excel options > formulas > iteration area.

Maximum iterations<<<Make this from 100 to 1.

This stop excel to gives you circular references.

To hide your formula and lock the area.

Highlight the range. Right cilck>>format cells>protection>check the hide option>>ok

Then protect your whole sheet.

(If you need some ranges to be unlocked, then highlight these>right click>protection>UNCHECK the lock option and then protect your whole sheet.)

You need to unlock the cells which you wish to be available for editing after the protection of sheet.
So the approach -> select entire worksheet -> format cells -> uncheck "locked"
now select formula cells (f5-> alt +s -> f -> enter) -> format cells -> check "locked" and "hidden"
now protect the worksheet. done

Regards,
DILIPandey
<click on below * if this helps>

..........................

Hi Friends,

Thank for your efforts and time......Sorry to say that it is not working and not able to get the result what I am looking for.

My intention is not to show the formula when press F2 and the same time no body can change or edit the formulas. But it should work like a normal excel sheet.

is there any way to do the same in excel ?

Thanks in advance

You mean .. lets say you have formula in a1 as =sum(b1:b5) which produces result as 100 but when you select and press F2, you can see 100 but formula should be hidden... correct?


Regards,
DILIPandey
<click on below * if this helps>

I am awaiting your response to my query which I asked in post #9.

Also, regarding

they will enter the CTC amount and percentage of Bonus

, I have already shown you a way - refer post #6 above.

Hope you are not expecting that someone else will do this for you


Regards,
DILIPandey
<click on below * if this helps>

sure...

Please post that solution here as well so that others can also learn who may land up here via internet search. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

I do not think your problems are difficult to resolve. It seems that you are struggling to effectively apply the advice and guidance that has been offered.

You may want to consider using the Commercial Services Forum where you can pay for someone to take on the end to end task of setting up your workbook.

http://www.excelforum.com/commercial-services/

You will need to pay for points which you can allocate to an individual who agrees to resolve the problem.

Regards, TMS


PS: if you are not going to pursue this thread here, you might consider marking it Solved.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Originally Posted by Venkata Krishna

....

My intention is not to show the formula when press F2 and the same time no body can change or edit the formulas. But it should work like a normal excel sheet.

is there any way to do the same in excel ?

Thanks in advance

yr solution is in post#2 and #3

how did u got it?

Originally Posted by john55

how did u got it?

why are you so curious? Don't be..!

Originally Posted by Fotis1991

why are you so curious? Don't be..!

)) sorry for being so curious! but I did not like the comment of post#12, is unfair, the OP rcvd solution(s).

Cheers



Regards,
DILIPandey
<click on below * if this helps>

........................

You're welcome. Thanks for the rep.


I'm glad you were able to work it out. It's always best when you understand how your spreadsheet works.

Regards, TMS