How To Use Range Names In Data Validation Criteria (Not For Lists)

  • Thread starter Thread starter KMH
  • Start date Start date
K

KMH

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
 
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.
 
Back
Top