Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
A member of this forum provided the following UDF to display a formula within
a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Personally, I would create a style that has the "Locked" flag selected
(as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Ken,
The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
To my knowledge it is not possible to use a UDF as a Data Validation
criteria. Based on that, the options that I can see are to either unshare the workbook, which you need to do to apply the validation rules anyway, and apply the method I outlined before. Honestly, I hope someone comes along and proves me wrong here, or provides an alternate route. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: Ken, The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
I'd use a UDF like this:
Option Explicit Function IsFormula(rng As Range) As Boolean IsFormula = CBool(rng.Cells(1).HasFormula) End Function Then I could put a formula in another cell (say B1) =isformula(a1) (and hide that column???) And use that cell for data|validation Custom =B1=TRUE But be aware that =1234 is a formula. Bob wrote: Ken, The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Dave,
Interesting solution. Given that my workbook contains over 130 columns, and over 550 rows of data, I'm reluctant to add the 6 additional hidden columns needed to do the job. The file size is huge as it is! Thanks all the same. I will definitely keep your solution in mind for future workbooks. Bob "Dave Peterson" wrote: I'd use a UDF like this: Option Explicit Function IsFormula(rng As Range) As Boolean IsFormula = CBool(rng.Cells(1).HasFormula) End Function Then I could put a formula in another cell (say B1) =isformula(a1) (and hide that column???) And use that cell for data|validation Custom =B1=TRUE But be aware that =1234 is a formula. Bob wrote: Ken, The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €œcustom€ Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €œNamed range cannot be found€ error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Ken,
Thanks for the info. I was really hoping that I could use a UDF as a Data Validation criteria. Thanks again for all your help. Bob "Ken Puls" wrote: To my knowledge it is not possible to use a UDF as a Data Validation criteria. Based on that, the options that I can see are to either unshare the workbook, which you need to do to apply the validation rules anyway, and apply the method I outlined before. Honestly, I hope someone comes along and proves me wrong here, or provides an alternate route. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: Ken, The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Happy to, Bob, only I wish I could have given better news.
I agree that it would be a very nice feature to have. I haven't tested to see if you can do that in Excel 2007 (i doubt it though), but maybe MS will develop it in the next release after? Late would be better than never, after all. :) Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: Ken, Thanks for the info. I was really hoping that I could use a UDF as a Data Validation criteria. Thanks again for all your help. Bob "Ken Puls" wrote: To my knowledge it is not possible to use a UDF as a Data Validation criteria. Based on that, the options that I can see are to either unshare the workbook, which you need to do to apply the validation rules anyway, and apply the method I outlined before. Honestly, I hope someone comes along and proves me wrong here, or provides an alternate route. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: Ken, The reason I am not using Excel's protection capabilities is because the workbook is "shared" and uses macros. Once shared, you cannot protect/unprotect a workbook. Hence, the reason I need to use Validation rules. Bob "Ken Puls" wrote: Personally, I would create a style that has the "Locked" flag selected (as it is by default). Highlight the entire sheet and unlock all the cells, then apply the style to your formula cells only. Protect the sheet and you're done. Is there any special reason why you're trying to protect your formula via a validation rule and not the built in protection methods? Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Bob wrote: A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following €ścustom€ť Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a €śNamed range cannot be found€ť error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying formulas using a Validation rule
Try this:
I don't know if this will work on a shared file but it works on non-shared files. Create this named formula: Name: IsFormula Refers to: =GET.CELL(48,INDIRECT("RC",FALSE)) Set the validation: Select the cells you want to validate. Allow: Custom Formula: =IsFormula Uncheck: Ignore blank I can't figure out why, but it doesn't work unless Ignore blank is unchecked. Biff "Bob" wrote in message ... A member of this forum provided the following UDF to display a formula within a cell: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function I tried creating the following "custom" Validation rule for cell A1 (which happens to contain a formula): =LEFT(GetFormula(A1),1)="=" but it is not working correctly (I get a "Named range cannot be found" error message). I want to ensure that someone doesn't inadvertently over-write a cell containing a formula. Can anyone show me how to do this using a Validation rule? I am aware of a solution using Conditional Formatting, but I really need to use a Validation rule. Any help would be greatly appreciated. Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Error | Excel Worksheet Functions | |||
Please help with Statistics Formulas in Excel | Excel Discussion (Misc queries) | |||
Formulas not recognizing new data | Excel Discussion (Misc queries) | |||
validation rule | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) |