Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Dermot
 
Posts: n/a
Default 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).

  #2   Report Post  
Posted to microsoft.public.excel.misc
damorrison
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
damorrison
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dermot
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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$9 9),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
  #7   Report Post  
Posted to microsoft.public.excel.misc
Dermot
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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$9 9),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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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$9 9),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
  #9   Report Post  
Posted to microsoft.public.excel.misc
Dermot
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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(initi als!$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$9 9),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

  #10   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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(initi als!$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$9 9),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


  #11   Report Post  
Posted to microsoft.public.excel.misc
Dermot
 
Posts: n/a
Default Validation Data using Validation Table cell range.....

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(initi als!$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$9 9),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

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Validation Data using Validation Table cell range.....

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance






"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

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Validation Data using Validation Table cell range.....

First, if you're using data|validation, I would have guessed that your formula
would have been:

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

This is one column wide.

It'll grow/contract with the amount of data in column A of sheet1. (Don't leave
any gaps.)

#1. You can have a macro that would add names, but personally, I'd just update
that list on sheet1 column A.

#2. I don't get that error. You'll have to explain how you did (in more
detail).



Dermot wrote:

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance

"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
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Validation Data using Validation Table cell range.....

Hello Dave
Thanks for the reply.

You are correct to say I am using the formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

and not what I posted previously in error.... I have just been going round
in circles to the point where I am dizzy!!!

I'll start fresh and see if I can figure out where I am going wrong, and
post back if I can better explain myself.





"Dave Peterson" wrote:

First, if you're using data|validation, I would have guessed that your formula
would have been:

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

This is one column wide.

It'll grow/contract with the amount of data in column A of sheet1. (Don't leave
any gaps.)

#1. You can have a macro that would add names, but personally, I'd just update
that list on sheet1 column A.

#2. I don't get that error. You'll have to explain how you did (in more
detail).



Dermot wrote:

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance

"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
.

  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Validation Data using Validation Table cell range.....

Hello Dave


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

I don't know how but I had spaces in the worksheet name Sheet 1 should have
been sheet1.

I made hard worik of this... couldn't see my problem for looking at it.

Thanks

"Dave Peterson" wrote:

First, if you're using data|validation, I would have guessed that your formula
would have been:

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

This is one column wide.

It'll grow/contract with the amount of data in column A of sheet1. (Don't leave
any gaps.)

#1. You can have a macro that would add names, but personally, I'd just update
that list on sheet1 column A.

#2. I don't get that error. You'll have to explain how you did (in more
detail).



Dermot wrote:

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance

"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
.



  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Validation Data using Validation Table cell range.....

If you're not sure if you need quotes around the sheet name, you can always add
the apostrophes--excel will remove them if it doesn't want them:

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

And sometimes I'll even rename my worksheet to something easy, like: A

Then I can use:
=OFFSET(A!$A$1,0,0,COUNTA(A!$A:$A),1)

And then when I rename the sheet to what it really should be, excel will fix my
formula.

Dermot wrote:

Hello Dave

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

I don't know how but I had spaces in the worksheet name Sheet 1 should have
been sheet1.

I made hard worik of this... couldn't see my problem for looking at it.

Thanks

"Dave Peterson" wrote:

First, if you're using data|validation, I would have guessed that your formula
would have been:

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

This is one column wide.

It'll grow/contract with the amount of data in column A of sheet1. (Don't leave
any gaps.)

#1. You can have a macro that would add names, but personally, I'd just update
that list on sheet1 column A.

#2. I don't get that error. You'll have to explain how you did (in more
detail).



Dermot wrote:

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance

"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
  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Validation Data using Validation Table cell range.....

Hello Dave

Thanks for the further explanations.



"Dave Peterson" wrote:

If you're not sure if you need quotes around the sheet name, you can always add
the apostrophes--excel will remove them if it doesn't want them:

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

And sometimes I'll even rename my worksheet to something easy, like: A

Then I can use:
=OFFSET(A!$A$1,0,0,COUNTA(A!$A:$A),1)

And then when I rename the sheet to what it really should be, excel will fix my
formula.

Dermot wrote:

Hello Dave

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

I don't know how but I had spaces in the worksheet name Sheet 1 should have
been sheet1.

I made hard worik of this... couldn't see my problem for looking at it.

Thanks

"Dave Peterson" wrote:

First, if you're using data|validation, I would have guessed that your formula
would have been:

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

This is one column wide.

It'll grow/contract with the amount of data in column A of sheet1. (Don't leave
any gaps.)

#1. You can have a macro that would add names, but personally, I'd just update
that list on sheet1 column A.

#2. I don't get that error. You'll have to explain how you did (in more
detail).



Dermot wrote:

Hello Dave
It's a while since I looked at this posting.
Having reviewed the excellent explanation at:

http://contextures.com/xlNames01.html#Dynamic
I wondered if you could answer some further questions please.

Assuming I am entering names using data validation in a column D1:D6 using a
Dynamic Name Range:
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA (Sheet1!$1:$1))

Question 1
Is it possible to have names not previously available in the list
automatically added to the list if entered manually?

Question 2
Is it possible to have the Dynamic Named Range tables on another Worksheet
without an error message being generated which reflects the data has to be on
the same worksheet?

Thanks in advance

"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
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I copy a table from Word into one cell in Excel without losing data? (Office 97) [email protected] Excel Discussion (Misc queries) 2 November 29th 05 08:22 PM
Input Cell in One variable data table Dottore Magistrale Excel Worksheet Functions 0 August 30th 05 06:25 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Conditional data validation (using a filtered range?) Simon Excel Worksheet Functions 0 February 15th 05 03:39 PM
Data Table - does it work with DDE links and Stock Tickers? Post Tenebras Lux Excel Worksheet Functions 0 December 1st 04 06:17 PM


All times are GMT +1. The time now is 09:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"