Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Use Range Names In Data Validation Criteria (Not For Lists)
Why can't I use range names in data validation criteria other than lists?
You can use range names to reference drop down lists, but not in the Formula Criteria for Decimal or Custom. Background...I am trying to use data validation as a simpler cell protection technique. I don't want to password protect because I want to leave flexibility in the spreadsheet, but I do want to prevent people from over writing certain formulas accidentally. I currently already have the spreadsheet formatted so these cells have a certain font color and I wanted to use VB to identify those cells and automatically add a data validation to each cell of that color making the criteria that it be equal to the decimal (or custom) formula that is in the cell. The VB code works, but my problem is that I use range names extensively in my formulas to make them easier to read and when I record the formula in VB to use as the data validation criteria, it works but does nothing unless I give it actual addresses not range names. I am using Excel 2007 but need backwards compatibility to 2003 right now. I need to be able to either... 1. use range names in Data validation somehow 2. VB convert range names in Data Validation easily but some of the formulas are quite complex which is why I use range names in first place. One range name at a time in each formula would be painful so is there a setting to get a formula automatically convertered into an address format that doesn't use range names. Thanks, Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Use Range Names In Data Validation Criteria (Not For Lists)
Anyone have any ideas?? Best I have come up with is VB code to basically do
a search and replace with the entire database of range names for each formula and then for each possible range name, but there has to be a quicker route than that. "KMH" wrote: Why can't I use range names in data validation criteria other than lists? You can use range names to reference drop down lists, but not in the Formula Criteria for Decimal or Custom. Background...I am trying to use data validation as a simpler cell protection technique. I don't want to password protect because I want to leave flexibility in the spreadsheet, but I do want to prevent people from over writing certain formulas accidentally. I currently already have the spreadsheet formatted so these cells have a certain font color and I wanted to use VB to identify those cells and automatically add a data validation to each cell of that color making the criteria that it be equal to the decimal (or custom) formula that is in the cell. The VB code works, but my problem is that I use range names extensively in my formulas to make them easier to read and when I record the formula in VB to use as the data validation criteria, it works but does nothing unless I give it actual addresses not range names. I am using Excel 2007 but need backwards compatibility to 2003 right now. I need to be able to either... 1. use range names in Data validation somehow 2. VB convert range names in Data Validation easily but some of the formulas are quite complex which is why I use range names in first place. One range name at a time in each formula would be painful so is there a setting to get a formula automatically convertered into an address format that doesn't use range names. Thanks, Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation to range names for Chart Source Data | Charts and Charting in Excel | |||
Data Validation Lists | Excel Discussion (Misc queries) | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
Data Validation Lists | Links and Linking in Excel |