passing formulas

[Guest]

Hello anyone

Need a little help. I want to take input from one worksheet, have that input to be calculated on a seperate worksheet, than post the results back on the original worksheet that it was entered in on. The whole reason for this is so that the formula cannot been seen by the user. I'm not at the expert level so the simpliest this can be said would be much appreciated. Thank you

 

[RagDyer]

You really don't have to go to those measures to hide your formula.
You can accomplish this on the original sheet by using:
<Format> <Cell> <Protection> tab,
and checking the "Hidden" box.

Now, just protect the sheet, and the only thing visible in the cell will be
the results of the formula, and the formula bar itself will be *empty*.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello anyone,

Need a little help. I want to take input from one worksheet, have that
input to be calculated on a seperate worksheet, than post the results back
on the original worksheet that it was entered in on. The whole reason for
this is so that the formula cannot been seen by the user. I'm not at the
expert level so the simpliest this can be said would be much appreciated.
Thank you

 

[Guest]

Thanks
But I need the worksheet to be unprotected so I can give it to clients to enter in data. Anyone else have a solution?

 

[Frank Kabel]

Hi Mike
you can protect the sheet and allow data entry:
- select the cells for which you want to allow entries
- goto 'Format -cells - Protection' and uncheck 'Locked'
- now protect the worksheet (with the formula settings described in the
previous posts)

Result: formulas are not visible, data entry in selected cells possible

 

[Norman Harker]

Hi Mike!

Let's try to give a simple explanation.

Protection of entries in Excel is a two stage process:

1. Address your mind to the cells that you want to allow the user to
have access to through the Format > Cells > Protection dialog
2. Impose protection using the Tools > Protection > Protect Sheet
dialog.

No protection exists until it is imposed by step 2.

If you execute step 2 without changing the protection status of any
cells, then ALL cells in the sheet are locked from access by the user
until protection of the sheet is removed using the Tools > Protection

Protect sheet dialog.

You can allow users to access some cells by removing the "Locked"
status in Format > Cells > Protection but you can only do this before
you protect the sheet (or if you remove the protection of the sheet).

You can allow users to access all cells in a sheet other than selected
cells by using the following process:

Select all cells in the sheet by pressing the button at the
intersection of row and column headings
Format > Cells > Protection
Remove check from "Locked"
OK

Select the cells that you want to protect
Format > Cells > Protection
Place check in "Locked"
OK

Tools > Protection > Protect Sheet
Give and confirm the password

By default, all cells are viewable in the formula bar. However if the
sheet is protected, formulas are only viewable for any cell as long as
you have NOT placed a check against "Hidden" using the Format > Cells
Protection dialog.

If you want to impose an automatic hide to all of your formula cells
then I would suggest that you first place a check on Hide for all
cells in the sheet before you start the process of deciding which
cells you want the user to have access to. You can achieve hiding all
cells by selecting all cells and using the Format > Cells > Protection

Place check on Hidden > OK.

Once this is done, you can then decide which cells you want to give
the user access to and remove the Locked and Hidden status from those
cells.

You'll find it a useful tip to color code the cells that you allow the
user to have access to. You can do this at the same time that you
remove the locked status. This way, the user will immediately see
which cells they can enter data in.


But finally, don't regard protection or hiding as being very secure.
Irrespective of all your efforts and carefully designed passwords,
"internal" Excel protection is very easily removed using workbooks
such as that downloadable from:

http://www.mcgimpsey.com/excel/removepwords.html



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks,
But I need the worksheet to be unprotected so I can give it to

clients to enter in data. Anyone else have a solution?

 

[Norman Harker]

Hi Mike!

Thanks for thanks. I just knew that a full explanation would allow you
to do what you wanted.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

thank you very much Norman for the solution and for your time, that

was the answer I was looking for.