Data validation using dynamic named range as input

[El_Diabolo]

Hi, All,

Sorry, but my brain refuses to work - again. To validate data one can:

1. Manually highlight the range to be validated, and fill the Data Validation prompts, or
2. Create a table and then define a named range within the table and fill in the prompts.

However, when I was using Excel 2003 I seem to remember creating a dynamic named range (something similar to a table named range) and using that as the data to be validated.
Unfortunately I cannot remember, or figure out despite lots of searching, how to use a dynamic named range as the input. Creating the range is fine, but using it, apparently, is beyond me. I know I could, and probably should, and probably will, use the table method, but this is really bugging me now. Please take pity on a stupid old man and remind me how to do it.

Many thanks.

[SamT]

Use CountA for text ranges, or Count for numeric ranges. "A200" must be larger than largest possible range

Named Range formula is

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

Range Starts in A1.

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

[Jan Karel Pieterse]

In Excel 2003 you can convert the range to a list and treat that as if it were an Excel 2007/2010 table. No dynamic range names needed.

[El_Diabolo]

Thank you all for your replies, gentlemen.

SamT: Thanks for your help, but I am perhaps misunderstanding the info you supplied. I know that the formats you gave will define a range dynamically, but what I don't know is how to use that range as the data to be validated(equivalent to highlighting a range manually), rather than the data against which validation will be made.

Paul: I apologise if I didn't follow the forum rules. I am trying to validate a range of data.

Jan Karel: Thanks for the info. Looks just the job.

Best regards to all.

[Paul_Hossler]

Are you working in 2003?

In 2010, I defined a workbook level dynamic range named 'DataValues'

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

just like you and others

I used Data Validation to reference this range on some cells on Sheet1

Did I misunderstand the question?

Paul


PS. It's always a good idea to provide the Excel version you're using

[El_Diabolo]

Hi Paul,

Thank you for your reply. Again I apologise for not saying which version of Excel I amusing. I seem to remember specifying this when I registered and I assumed that would be available to anyone needing to know. Never assume anything! I am using Excel 2010.
I don't think you are misunderstanding the question, rather that I am not explaining it adequately. I will try to do so now. The dialog box you provide is a good place for me to begin. In this dialog box there is a "Source" field, against which data will be validated (if I've got that wrong then there's no hope for me). As you say I have defined a dynamic named range for this and that works fine. My question is how can I similarly define the range which is to be validated (rather than the range which does the validating)? In other words, if I want to validate the range A1:A50, for example, I could highlight that range and then go through the Data Validation dialog box, specifying the source, whatever that may be, against which A1:A50 is to be validated. What I am trying to do is rather than specify a static range such as A1:A50, whether named or not, is make that range dynamic. What I would call dynamic input. I hope that explains the requirement.

As i mentioned in my original post the Excel Table feature provides a solution, although, as I have just discovered, there may be other wrinkles introduced with it, e.g. sorting in VBA. However, that's just all part of the learning process and I look forward to it.

Thank you for your time and expertise. Best regards.

[Jan Karel Pieterse]

You cannot make a validation range dynamic and even if the range is converted to a table, the cells beneath the table will not have validation. After entering a value in one of the cells beneath the table you get a new row in your table, which now has the validation in place. But that is AFTER adding the row.

[El_Diabolo]

Thank you, Jan Karel. That answers my question perfectly. Best regards.