Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this person as Tom as this person has asked for anonymity of his identity.
As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here]. Tom has asked me a question related to data validation for a pre-defined list. He asks - when defining the validation rule for a list, we have to put a range where the list has been defined. Now when you are defining the rule, it asks for the list range and you have to either manually enter the range or you can select the range with the help pf mouse.
At this stage, there is one thing you cannot do, i.e. input the range which falls out of that sheet. What it means is you cannot select any range which is not on the same sheet where the validation is being put.
Tom is correct partially. He is correct when he said that users cannot select the range which is on the other sheet, but it is not true that you cannot enter the range that is not on the same sheet. One can very well enter the range manually and have the list located on any sheet other than where it is being used for validation.
One simply has to mention the full range of the list (the address where the list is located) together with the sheet name and it will work normally. Just remember to put a '=' sign so that the data validation works fine. For an example - I will refer to a list which is on 'sheet2' and I am applying this validation on a cell in 'sheet1'. My formula for this would be:
= 'Sheet1'!A4:A10
There is another way you can do this (and in my opinion this is the best way) - using Range Names.
A Range Name is a name given to a cell or range of cells. One can use this name anywhere in the workbook for any kind of processing without having to worry about the sheet names and the cell ranges. In our case, while using range name, the validation rule should simply refer to the range name given to the list. The list address should instead show this formula (assuming the range name given is 'ListName'):
=ListName
...and that is it!
Range Names are one of the coolest tool that Excel has provided. I have not yet written about it, but will soon do it. Range Names can actually make your life very simple when writing complex formulas and making heavy and dynamic Excel models.
Thursday, August 6, 2009
Working out with Data Validation list
As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here]. Tom has asked me a question related to data validation for a pre-defined list. He asks - when defining the validation rule for a list, we have to put a range where the list has been defined. Now when you are defining the rule, it asks for the list range and you have to either manually enter the range or you can select the range with the help pf mouse.
At this stage, there is one thing you cannot do, i.e. input the range which falls out of that sheet. What it means is you cannot select any range which is not on the same sheet where the validation is being put.
Tom is correct partially. He is correct when he said that users cannot select the range which is on the other sheet, but it is not true that you cannot enter the range that is not on the same sheet. One can very well enter the range manually and have the list located on any sheet other than where it is being used for validation.
One simply has to mention the full range of the list (the address where the list is located) together with the sheet name and it will work normally. Just remember to put a '=' sign so that the data validation works fine. For an example - I will refer to a list which is on 'sheet2' and I am applying this validation on a cell in 'sheet1'. My formula for this would be:
= 'Sheet1'!A4:A10
There is another way you can do this (and in my opinion this is the best way) - using Range Names.
A Range Name is a name given to a cell or range of cells. One can use this name anywhere in the workbook for any kind of processing without having to worry about the sheet names and the cell ranges. In our case, while using range name, the validation rule should simply refer to the range name given to the list. The list address should instead show this formula (assuming the range name given is 'ListName'):
=ListName
...and that is it!
Range Names are one of the coolest tool that Excel has provided. I have not yet written about it, but will soon do it. Range Names can actually make your life very simple when writing complex formulas and making heavy and dynamic Excel models.
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment