Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I copy a table from Word into one cell in Excel without losing data? (Office 97) | Excel Discussion (Misc queries) | |||
Input Cell in One variable data table | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Conditional data validation (using a filtered range?) | Excel Worksheet Functions | |||
Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions |