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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


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?
Click to expand...
 
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?
 
You meant A1:A65536, right?

Try this in B1 of a test worksheet.

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


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?
Click to expand...
 
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
 
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
 
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).
 
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.
 
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
 
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.
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
Click to expand...
 
Back
Top