Validation Data using Validation Table cell range.....

I have included a few additional empty cells in my list so I can add a few
more names in the validation drop down list.....not sure if this is
relevant......

when I click on the dropdown arrow, the list appears empty because the
slider is not positioned at the top. Is there a property I can set to ensure
the list slider is at the top showing all my options? (The contents of the
list in my cells are peoples initials).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$B$7" Then
Set cell =
ActiveWorkbook.Names.Item("MyList").RefersToRange.Range("A1")
Target.Value = cell.Value
End If
End Sub

for this example, you will need to name your list range
if you don't know how to do this let us know,
this example has the data validation in cell B7,
go to data validation, list and in the list box enter this =MyList
right click on the sheet tab and view codes
the box that says (General) hit the arrow and select worksheet
copy and paste the above code under the word option explicit
Dave

because the one line of code doesn't fit you will need to place the
one line that starts with
ActivWorkbook.Names......
to the right of the "=" sign
Dave

Instead of reserving empty cells, you could use a dynamic range name.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Dermot wrote:
>
> I have included a few additional empty cells in my list so I can add a few
> more names in the validation drop down list.....not sure if this is
> relevant......
>
> when I click on the dropdown arrow, the list appears empty because the
> slider is not positioned at the top. Is there a property I can set to ensure
> the list slider is at the top showing all my options? (The contents of the
> list in my cells are peoples initials).

--

Dave Peterson

Thanks for the reply Dave
The Dynamic Range sounds good to me, but I have read a little further and
have a few questions.
My list is located in three cells DC9:DC11. The contain staff initials.
Here is what I understand of creaing a working formula:

=(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)

Questions:-
1. I don't understand how to work out the row and column offsets in the
formula?
2. The one at the end indicates the number of Dynamic Columns?
3. How do I enter additional staff initials when the range currently
consists of 3 cells. I don't quite understand how the dynamic adjustment of
the range works automatically if I add a futher initial under the last
initials entry...wouldn't it be outside the initial range of 3
cells...puzzled?
4. Is there an expression builder available, which assists in creating the
formula using cell dragging and selecting methods, or do I have to create
some of it manually?
Thanks
Dermot

"Dave Peterson" wrote:

> Instead of reserving empty cells, you could use a dynamic range name.
>
> See Debra Dalgeish's site for some nice tips:
> http://contextures.com/xlNames01.html#Dynamic
>
> Dermot wrote:
> >
> > I have included a few additional empty cells in my list so I can add a few
> > more names in the validation drop down list.....not sure if this is
> > relevant......
> >
> > when I click on the dropdown arrow, the list appears empty because the
> > slider is not positioned at the top. Is there a property I can set to ensure
> > the list slider is at the top showing all my options? (The contents of the
> > list in my cells are peoples initials).
>
> --
>
> Dave Peterson
>

First, I try to put all my tables on separate worksheets (instead of just hiding
them to the far right). Sometimes, I'll even dedicate the worksheet to one
table (and use multiple worksheets--even hiding them).

But I'd plop those initials into a worksheet named Initials in A1.

Then use Insert|name|Define
myInitials
refers to:
=offset(initials!$a$1,0,0,counta(initials!$a:$a),1)

And start your data in A1 and don't leave any empty cells in the middle.

But you could use:

=OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)

No gaps in the list (dc9:dc99).

That first 0,0 says to start in DC9 and not move.
How many rows to go down is given by counta()
and make it 1 column wide.

I like to define my name, then add a bit of data. Then I hit edit|goto and type
in the name I used. If I get the right stuff selected, I'm happy as a clam.

=====
One more thing about having these kinds of tables on separate sheets. It really
makes it more convenient to update the tables. But it also makes it easier to
update the non-table stuff. I can insert/delete entire rows and columns without
having to worry about what becomes of my table's data.

Dermot wrote:
>
> Thanks for the reply Dave
> The Dynamic Range sounds good to me, but I have read a little further and
> have a few questions.
> My list is located in three cells DC9:DC11. The contain staff initials.
> Here is what I understand of creaing a working formula:
>
> =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
>
> Questions:-
> 1. I don't understand how to work out the row and column offsets in the
> formula?
> 2. The one at the end indicates the number of Dynamic Columns?
> 3. How do I enter additional staff initials when the range currently
> consists of 3 cells. I don't quite understand how the dynamic adjustment of
> the range works automatically if I add a futher initial under the last
> initials entry...wouldn't it be outside the initial range of 3
> cells...puzzled?
> 4. Is there an expression builder available, which assists in creating the
> formula using cell dragging and selecting methods, or do I have to create
> some of it manually?
> Thanks
> Dermot
>
> "Dave Peterson" wrote:
>
> > Instead of reserving empty cells, you could use a dynamic range name.
> >
> > See Debra Dalgeish's site for some nice tips:
> > http://contextures.com/xlNames01.html#Dynamic
> >
> > Dermot wrote:
> > >
> > > I have included a few additional empty cells in my list so I can add a few
> > > more names in the validation drop down list.....not sure if this is
> > > relevant......
> > >
> > > when I click on the dropdown arrow, the list appears empty because the
> > > slider is not positioned at the top. Is there a property I can set to ensure
> > > the list slider is at the top showing all my options? (The contents of the
> > > list in my cells are peoples initials).
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi Dave,
Thanks for the reply.
Thanks for the tip regading using a separate table sheet. the point
regarding deleting rows etc is something I overlooked, thanks.....please
advise further...
I have moved the Initials table to another worksheet named initials as
suggested.
Defined the name "Initials".
I entered the formula =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
as a validation list.
Now I get an error stating that validation criteria cannot refer to another
worksheet.
Questions
1. counta(initials!$a:$a)................using absolute references range
$a:$a......doesn't this just refer to the one cell, although I am using 3
cell which contain initial data?

2. What will I have overlooked, when I get the validation error?


"Dave Peterson" wrote:

> First, I try to put all my tables on separate worksheets (instead of just hiding
> them to the far right). Sometimes, I'll even dedicate the worksheet to one
> table (and use multiple worksheets--even hiding them).
>
> But I'd plop those initials into a worksheet named Initials in A1.
>
> Then use Insert|name|Define
> myInitials
> refers to:
> =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
>
> And start your data in A1 and don't leave any empty cells in the middle.
>
> But you could use:
>
> =OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)
>
> No gaps in the list (dc9:dc99).
>
> That first 0,0 says to start in DC9 and not move.
> How many rows to go down is given by counta()
> and make it 1 column wide.
>
> I like to define my name, then add a bit of data. Then I hit edit|goto and type
> in the name I used. If I get the right stuff selected, I'm happy as a clam.
>
> =====
> One more thing about having these kinds of tables on separate sheets. It really
> makes it more convenient to update the tables. But it also makes it easier to
> update the non-table stuff. I can insert/delete entire rows and columns without
> having to worry about what becomes of my table's data.
>
> Dermot wrote:
> >
> > Thanks for the reply Dave
> > The Dynamic Range sounds good to me, but I have read a little further and
> > have a few questions.
> > My list is located in three cells DC9:DC11. The contain staff initials.
> > Here is what I understand of creaing a working formula:
> >
> > =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
> >
> > Questions:-
> > 1. I don't understand how to work out the row and column offsets in the
> > formula?
> > 2. The one at the end indicates the number of Dynamic Columns?
> > 3. How do I enter additional staff initials when the range currently
> > consists of 3 cells. I don't quite understand how the dynamic adjustment of
> > the range works automatically if I add a futher initial under the last
> > initials entry...wouldn't it be outside the initial range of 3
> > cells...puzzled?
> > 4. Is there an expression builder available, which assists in creating the
> > formula using cell dragging and selecting methods, or do I have to create
> > some of it manually?
> > Thanks
> > Dermot
> >
> > "Dave Peterson" wrote:
> >
> > > Instead of reserving empty cells, you could use a dynamic range name.
> > >
> > > See Debra Dalgeish's site for some nice tips:
> > > http://contextures.com/xlNames01.html#Dynamic
> > >
> > > Dermot wrote:
> > > >
> > > > I have included a few additional empty cells in my list so I can add a few
> > > > more names in the validation drop down list.....not sure if this is
> > > > relevant......
> > > >
> > > > when I click on the dropdown arrow, the list appears empty because the
> > > > slider is not positioned at the top. Is there a property I can set to ensure
> > > > the list slider is at the top showing all my options? (The contents of the
> > > > list in my cells are peoples initials).
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

When you're in the Data|Validation dialog, use this:

=Initials

You can cheat that error message by using the named range.

$a:$a is the whole column.

=counta($a:$a) will count the number of cells in column A that have something in
them.



Dermot wrote:
>
> Hi Dave,
> Thanks for the reply.
> Thanks for the tip regading using a separate table sheet. the point
> regarding deleting rows etc is something I overlooked, thanks.....please
> advise further...
> I have moved the Initials table to another worksheet named initials as
> suggested.
> Defined the name "Initials".
> I entered the formula =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> as a validation list.
> Now I get an error stating that validation criteria cannot refer to another
> worksheet.
> Questions
> 1. counta(initials!$a:$a)................using absolute references range
> $a:$a......doesn't this just refer to the one cell, although I am using 3
> cell which contain initial data?
>
> 2. What will I have overlooked, when I get the validation error?
>
> "Dave Peterson" wrote:
>
> > First, I try to put all my tables on separate worksheets (instead of just hiding
> > them to the far right). Sometimes, I'll even dedicate the worksheet to one
> > table (and use multiple worksheets--even hiding them).
> >
> > But I'd plop those initials into a worksheet named Initials in A1.
> >
> > Then use Insert|name|Define
> > myInitials
> > refers to:
> > =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> >
> > And start your data in A1 and don't leave any empty cells in the middle.
> >
> > But you could use:
> >
> > =OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)
> >
> > No gaps in the list (dc9:dc99).
> >
> > That first 0,0 says to start in DC9 and not move.
> > How many rows to go down is given by counta()
> > and make it 1 column wide.
> >
> > I like to define my name, then add a bit of data. Then I hit edit|goto and type
> > in the name I used. If I get the right stuff selected, I'm happy as a clam.
> >
> > =====
> > One more thing about having these kinds of tables on separate sheets. It really
> > makes it more convenient to update the tables. But it also makes it easier to
> > update the non-table stuff. I can insert/delete entire rows and columns without
> > having to worry about what becomes of my table's data.
> >
> > Dermot wrote:
> > >
> > > Thanks for the reply Dave
> > > The Dynamic Range sounds good to me, but I have read a little further and
> > > have a few questions.
> > > My list is located in three cells DC9:DC11. The contain staff initials.
> > > Here is what I understand of creaing a working formula:
> > >
> > > =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
> > >
> > > Questions:-
> > > 1. I don't understand how to work out the row and column offsets in the
> > > formula?
> > > 2. The one at the end indicates the number of Dynamic Columns?
> > > 3. How do I enter additional staff initials when the range currently
> > > consists of 3 cells. I don't quite understand how the dynamic adjustment of
> > > the range works automatically if I add a futher initial under the last
> > > initials entry...wouldn't it be outside the initial range of 3
> > > cells...puzzled?
> > > 4. Is there an expression builder available, which assists in creating the
> > > formula using cell dragging and selecting methods, or do I have to create
> > > some of it manually?
> > > Thanks
> > > Dermot
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Instead of reserving empty cells, you could use a dynamic range name.
> > > >
> > > > See Debra Dalgeish's site for some nice tips:
> > > > http://contextures.com/xlNames01.html#Dynamic
> > > >
> > > > Dermot wrote:
> > > > >
> > > > > I have included a few additional empty cells in my list so I can add a few
> > > > > more names in the validation drop down list.....not sure if this is
> > > > > relevant......
> > > > >
> > > > > when I click on the dropdown arrow, the list appears empty because the
> > > > > slider is not positioned at the top. Is there a property I can set to ensure
> > > > > the list slider is at the top showing all my options? (The contents of the
> > > > > list in my cells are peoples initials).
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi Dave,
Thanks for the quick reply, very much appreciated, I now have it working and
the sugesion to hide sheets of this kind is a great tip too.
Just as you suggested...=offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
works fine now that I am using the defined name.....now I understand why I
had to define a named range.

I thought $a:$a65536 was a full column, and something like $a:$a would be
considered a sort of loop error?

"Dave Peterson" wrote:

> When you're in the Data|Validation dialog, use this:
>
> =Initials
>
> You can cheat that error message by using the named range.
>
> $a:$a is the whole column.
>
> =counta($a:$a) will count the number of cells in column A that have something in
> them.
>
>
>
> Dermot wrote:
> >
> > Hi Dave,
> > Thanks for the reply.
> > Thanks for the tip regading using a separate table sheet. the point
> > regarding deleting rows etc is something I overlooked, thanks.....please
> > advise further...
> > I have moved the Initials table to another worksheet named initials as
> > suggested.
> > Defined the name "Initials".
> > I entered the formula =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > as a validation list.
> > Now I get an error stating that validation criteria cannot refer to another
> > worksheet.
> > Questions
> > 1. counta(initials!$a:$a)................using absolute references range
> > $a:$a......doesn't this just refer to the one cell, although I am using 3
> > cell which contain initial data?
> >
> > 2. What will I have overlooked, when I get the validation error?
> >
> > "Dave Peterson" wrote:
> >
> > > First, I try to put all my tables on separate worksheets (instead of just hiding
> > > them to the far right). Sometimes, I'll even dedicate the worksheet to one
> > > table (and use multiple worksheets--even hiding them).
> > >
> > > But I'd plop those initials into a worksheet named Initials in A1.
> > >
> > > Then use Insert|name|Define
> > > myInitials
> > > refers to:
> > > =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > >
> > > And start your data in A1 and don't leave any empty cells in the middle.
> > >
> > > But you could use:
> > >
> > > =OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)
> > >
> > > No gaps in the list (dc9:dc99).
> > >
> > > That first 0,0 says to start in DC9 and not move.
> > > How many rows to go down is given by counta()
> > > and make it 1 column wide.
> > >
> > > I like to define my name, then add a bit of data. Then I hit edit|goto and type
> > > in the name I used. If I get the right stuff selected, I'm happy as a clam.
> > >
> > > =====
> > > One more thing about having these kinds of tables on separate sheets. It really
> > > makes it more convenient to update the tables. But it also makes it easier to
> > > update the non-table stuff. I can insert/delete entire rows and columns without
> > > having to worry about what becomes of my table's data.
> > >
> > > Dermot wrote:
> > > >
> > > > Thanks for the reply Dave
> > > > The Dynamic Range sounds good to me, but I have read a little further and
> > > > have a few questions.
> > > > My list is located in three cells DC9:DC11. The contain staff initials.
> > > > Here is what I understand of creaing a working formula:
> > > >
> > > > =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
> > > >
> > > > Questions:-
> > > > 1. I don't understand how to work out the row and column offsets in the
> > > > formula?
> > > > 2. The one at the end indicates the number of Dynamic Columns?
> > > > 3. How do I enter additional staff initials when the range currently
> > > > consists of 3 cells. I don't quite understand how the dynamic adjustment of
> > > > the range works automatically if I add a futher initial under the last
> > > > initials entry...wouldn't it be outside the initial range of 3
> > > > cells...puzzled?
> > > > 4. Is there an expression builder available, which assists in creating the
> > > > formula using cell dragging and selecting methods, or do I have to create
> > > > some of it manually?
> > > > Thanks
> > > > Dermot
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Instead of reserving empty cells, you could use a dynamic range name.
> > > > >
> > > > > See Debra Dalgeish's site for some nice tips:
> > > > > http://contextures.com/xlNames01.html#Dynamic
> > > > >
> > > > > Dermot wrote:
> > > > > >
> > > > > > I have included a few additional empty cells in my list so I can add a few
> > > > > > more names in the validation drop down list.....not sure if this is
> > > > > > relevant......
> > > > > >
> > > > > > when I click on the dropdown arrow, the list appears empty because the
> > > > > > slider is not positioned at the top. Is there a property I can set to ensure
> > > > > > the list slider is at the top showing all my options? (The contents of the
> > > > > > list in my cells are peoples initials).
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You meant A1:A65536, right?

Try this in B1 of a test worksheet.

=sum(a1:a65536)
hit enter and look at the formula.



Dermot wrote:
>
> Hi Dave,
> Thanks for the quick reply, very much appreciated, I now have it working and
> the sugesion to hide sheets of this kind is a great tip too.
> Just as you suggested...=offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> works fine now that I am using the defined name.....now I understand why I
> had to define a named range.
>
> I thought $a:$a65536 was a full column, and something like $a:$a would be
> considered a sort of loop error?
>
> "Dave Peterson" wrote:
>
> > When you're in the Data|Validation dialog, use this:
> >
> > =Initials
> >
> > You can cheat that error message by using the named range.
> >
> > $a:$a is the whole column.
> >
> > =counta($a:$a) will count the number of cells in column A that have something in
> > them.
> >
> >
> >
> > Dermot wrote:
> > >
> > > Hi Dave,
> > > Thanks for the reply.
> > > Thanks for the tip regading using a separate table sheet. the point
> > > regarding deleting rows etc is something I overlooked, thanks.....please
> > > advise further...
> > > I have moved the Initials table to another worksheet named initials as
> > > suggested.
> > > Defined the name "Initials".
> > > I entered the formula =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > > as a validation list.
> > > Now I get an error stating that validation criteria cannot refer to another
> > > worksheet.
> > > Questions
> > > 1. counta(initials!$a:$a)................using absolute references range
> > > $a:$a......doesn't this just refer to the one cell, although I am using 3
> > > cell which contain initial data?
> > >
> > > 2. What will I have overlooked, when I get the validation error?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > First, I try to put all my tables on separate worksheets (instead of just hiding
> > > > them to the far right). Sometimes, I'll even dedicate the worksheet to one
> > > > table (and use multiple worksheets--even hiding them).
> > > >
> > > > But I'd plop those initials into a worksheet named Initials in A1.
> > > >
> > > > Then use Insert|name|Define
> > > > myInitials
> > > > refers to:
> > > > =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > > >
> > > > And start your data in A1 and don't leave any empty cells in the middle.
> > > >
> > > > But you could use:
> > > >
> > > > =OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)
> > > >
> > > > No gaps in the list (dc9:dc99).
> > > >
> > > > That first 0,0 says to start in DC9 and not move.
> > > > How many rows to go down is given by counta()
> > > > and make it 1 column wide.
> > > >
> > > > I like to define my name, then add a bit of data. Then I hit edit|goto and type
> > > > in the name I used. If I get the right stuff selected, I'm happy as a clam.
> > > >
> > > > =====
> > > > One more thing about having these kinds of tables on separate sheets. It really
> > > > makes it more convenient to update the tables. But it also makes it easier to
> > > > update the non-table stuff. I can insert/delete entire rows and columns without
> > > > having to worry about what becomes of my table's data.
> > > >
> > > > Dermot wrote:
> > > > >
> > > > > Thanks for the reply Dave
> > > > > The Dynamic Range sounds good to me, but I have read a little further and
> > > > > have a few questions.
> > > > > My list is located in three cells DC9:DC11. The contain staff initials.
> > > > > Here is what I understand of creaing a working formula:
> > > > >
> > > > > =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
> > > > >
> > > > > Questions:-
> > > > > 1. I don't understand how to work out the row and column offsets in the
> > > > > formula?
> > > > > 2. The one at the end indicates the number of Dynamic Columns?
> > > > > 3. How do I enter additional staff initials when the range currently
> > > > > consists of 3 cells. I don't quite understand how the dynamic adjustment of
> > > > > the range works automatically if I add a futher initial under the last
> > > > > initials entry...wouldn't it be outside the initial range of 3
> > > > > cells...puzzled?
> > > > > 4. Is there an expression builder available, which assists in creating the
> > > > > formula using cell dragging and selecting methods, or do I have to create
> > > > > some of it manually?
> > > > > Thanks
> > > > > Dermot
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Instead of reserving empty cells, you could use a dynamic range name.
> > > > > >
> > > > > > See Debra Dalgeish's site for some nice tips:
> > > > > > http://contextures.com/xlNames01.html#Dynamic
> > > > > >
> > > > > > Dermot wrote:
> > > > > > >
> > > > > > > I have included a few additional empty cells in my list so I can add a few
> > > > > > > more names in the validation drop down list.....not sure if this is
> > > > > > > relevant......
> > > > > > >
> > > > > > > when I click on the dropdown arrow, the list appears empty because the
> > > > > > > slider is not positioned at the top. Is there a property I can set to ensure
> > > > > > > the list slider is at the top showing all my options? (The contents of the
> > > > > > > list in my cells are peoples initials).
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

mmmm....I see what you mean Dave....I'll need to think about this.
Thanks for the great help
I'm a happy chappy
Dermot

"Dave Peterson" wrote:

> You meant A1:A65536, right?
>
> Try this in B1 of a test worksheet.
>
> =sum(a1:a65536)
> hit enter and look at the formula.
>
>
>
> Dermot wrote:
> >
> > Hi Dave,
> > Thanks for the quick reply, very much appreciated, I now have it working and
> > the sugesion to hide sheets of this kind is a great tip too.
> > Just as you suggested...=offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > works fine now that I am using the defined name.....now I understand why I
> > had to define a named range.
> >
> > I thought $a:$a65536 was a full column, and something like $a:$a would be
> > considered a sort of loop error?
> >
> > "Dave Peterson" wrote:
> >
> > > When you're in the Data|Validation dialog, use this:
> > >
> > > =Initials
> > >
> > > You can cheat that error message by using the named range.
> > >
> > > $a:$a is the whole column.
> > >
> > > =counta($a:$a) will count the number of cells in column A that have something in
> > > them.
> > >
> > >
> > >
> > > Dermot wrote:
> > > >
> > > > Hi Dave,
> > > > Thanks for the reply.
> > > > Thanks for the tip regading using a separate table sheet. the point
> > > > regarding deleting rows etc is something I overlooked, thanks.....please
> > > > advise further...
> > > > I have moved the Initials table to another worksheet named initials as
> > > > suggested.
> > > > Defined the name "Initials".
> > > > I entered the formula =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > > > as a validation list.
> > > > Now I get an error stating that validation criteria cannot refer to another
> > > > worksheet.
> > > > Questions
> > > > 1. counta(initials!$a:$a)................using absolute references range
> > > > $a:$a......doesn't this just refer to the one cell, although I am using 3
> > > > cell which contain initial data?
> > > >
> > > > 2. What will I have overlooked, when I get the validation error?
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > First, I try to put all my tables on separate worksheets (instead of just hiding
> > > > > them to the far right). Sometimes, I'll even dedicate the worksheet to one
> > > > > table (and use multiple worksheets--even hiding them).
> > > > >
> > > > > But I'd plop those initials into a worksheet named Initials in A1.
> > > > >
> > > > > Then use Insert|name|Define
> > > > > myInitials
> > > > > refers to:
> > > > > =offset(initials!$a$1,0,0,counta(initials!$a:$a),1)
> > > > >
> > > > > And start your data in A1 and don't leave any empty cells in the middle.
> > > > >
> > > > > But you could use:
> > > > >
> > > > > =OFFSET(Sheet1!$DC$9,0,0,COUNTa(Sheet1!$DC$9:$DC$99),1)
> > > > >
> > > > > No gaps in the list (dc9:dc99).
> > > > >
> > > > > That first 0,0 says to start in DC9 and not move.
> > > > > How many rows to go down is given by counta()
> > > > > and make it 1 column wide.
> > > > >
> > > > > I like to define my name, then add a bit of data. Then I hit edit|goto and type
> > > > > in the name I used. If I get the right stuff selected, I'm happy as a clam.
> > > > >
> > > > > =====
> > > > > One more thing about having these kinds of tables on separate sheets. It really
> > > > > makes it more convenient to update the tables. But it also makes it easier to
> > > > > update the non-table stuff. I can insert/delete entire rows and columns without
> > > > > having to worry about what becomes of my table's data.
> > > > >
> > > > > Dermot wrote:
> > > > > >
> > > > > > Thanks for the reply Dave
> > > > > > The Dynamic Range sounds good to me, but I have read a little further and
> > > > > > have a few questions.
> > > > > > My list is located in three cells DC9:DC11. The contain staff initials.
> > > > > > Here is what I understand of creaing a working formula:
> > > > > >
> > > > > > =(OFFSET(Sheet1!$DC$9,ROW OFFSET,COLUMN OFFSET,COUNTDC(Sheet1!$DC$9:$DC$11),1)
> > > > > >
> > > > > > Questions:-
> > > > > > 1. I don't understand how to work out the row and column offsets in the
> > > > > > formula?
> > > > > > 2. The one at the end indicates the number of Dynamic Columns?
> > > > > > 3. How do I enter additional staff initials when the range currently
> > > > > > consists of 3 cells. I don't quite understand how the dynamic adjustment of
> > > > > > the range works automatically if I add a futher initial under the last
> > > > > > initials entry...wouldn't it be outside the initial range of 3
> > > > > > cells...puzzled?
> > > > > > 4. Is there an expression builder available, which assists in creating the
> > > > > > formula using cell dragging and selecting methods, or do I have to create
> > > > > > some of it manually?
> > > > > > Thanks
> > > > > > Dermot
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Instead of reserving empty cells, you could use a dynamic range name.
> > > > > > >
> > > > > > > See Debra Dalgeish's site for some nice tips:
> > > > > > > http://contextures.com/xlNames01.html#Dynamic
> > > > > > >
> > > > > > > Dermot wrote:
> > > > > > > >
> > > > > > > > I have included a few additional empty cells in my list so I can add a few
> > > > > > > > more names in the validation drop down list.....not sure if this is
> > > > > > > > relevant......
> > > > > > > >
> > > > > > > > when I click on the dropdown arrow, the list appears empty because the
> > > > > > > > slider is not positioned at the top. Is there a property I can set to ensure
> > > > > > > > the list slider is at the top showing all my options? (The contents of the
> > > > > > > > list in my cells are peoples initials).
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>