Hi,
It is easy to see the formula even after applying protection as suggested
below. Just copy a blank cell and then Edit paste Special Formats over
the cell which is protected. The formula will be visible (even though the
cell will be protected [cannot edit, delete the contents]).
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Jacob Skaria" wrote in message
...
Try the below
--Select the cells in which you want users to enter data, then go to
FormatCellsProtection tab and uncheck 'Lock'.
--Select the formulas you want to be hidden. Go to Format - Cells -
Protection and select Hidden.
--From Tools ProtectionProtect Sheet. This will allow users to
enter data, but not change or see the formulas
If this post helps click Yes
---------------
Jacob Skaria
"Kevin" wrote:
Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.
do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?
Thanks again.
Kevin
--
Kevin
"Jacob Skaria" wrote:
Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)
If this post helps click Yes
---------------
Jacob Skaria
"Kevin" wrote:
Your test example works great. This is when the
data is all on the same Sheet.
However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help
--
Kevin
"Jacob Skaria" wrote:
Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11.
From menu Insert a Module and paste the below function.Close and
get back to
workbook and try the below formula.
Try this test...
In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33
Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)
If this post helps click Yes
---------------
Jacob Skaria
"Kevin" wrote:
Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?
I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.
Anyother thoughts ? thanks,
--
Kevin
"Jacob Skaria" wrote:
Do you mean...
Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)
MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)
End Function
If this post helps click Yes
---------------
Jacob Skaria
"Kevin" wrote:
I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.
Sheet#1 is the main sheet
Sheet#2 is my database sheet
In Sheet #1 The rangename "tabledata" is assigned as the
cell range
a1:g100 located in sheet#2
Needing help with the syntax for two things:
FUNCTION syntax in line 1 of the USD
Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4])
syntax
Var1 is what will be returned in Sheet#1
I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks
Kevin