|  | drop down list | | |
| | | nip |  |
|  Posted: Fri Jul 04, 2008 1:45 am Post subject: drop down list |  |
Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5 >
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible? |
| |
| | | Harald Staff | |
| | Posted: Fri Jul 04, 2008 7:48 am Post subject: Re: drop down list | |
| |  | |
I'm sure the array formula experts will have a far shorter solution, but
this should work:
="You hvave
"&INDEX(B1:B52;MATCH(VLOOKUP(TODAY();B1:B52;1;TRUE);B1:B52)+1)-TODAY()&"
days until deadline week
"&INDEX(A1:A52;MATCH(VLOOKUP(TODAY();B1:B52;1;TRUE);B1:B52)+1)
Error handling will lengthen it a lot (how much depending on excel version),
so I left that out. As is it needs a future date in B to work.
HTH. Best wishes Harald
"nip" <gbreath@gmail.com> skrev i melding
news:8dce6c59-ac94-4087-859d-fa7eadbf6333@q27g2000prf.googlegroups.com...
| Quote: | Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible? |
|
| |
| | | Harald Staff | |
| | Posted: Fri Jul 04, 2008 7:50 am Post subject: Re: drop down list | |
Oops. Wrong thread, wrong problem, wrong solution. Sorry :-)
"nip" <gbreath@gmail.com> skrev i melding
news:8dce6c59-ac94-4087-859d-fa7eadbf6333@q27g2000prf.googlegroups.com...
| Quote: | Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible? |
|
| |
| | | Graham H | |
| | Posted: Fri Jul 04, 2008 10:13 am Post subject: Re: drop down list | |
Hi,
If you use a dynamic named list you can add to the list and it will be picked up.
See details of how to create this here:-
LINK
HTH
Graham
nip wrote:
| Quote: | Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible? |
|
| |
| | | Shane Devenshire | |
| | Posted: Fri Jul 04, 2008 11:46 am Post subject: Re: drop down list | |
| | | |
Hi,
Three ways at least to do this:
1. Insert the new item between previous entries.
2. Use a dynamic range name:
- choose Formulas, Define Name and enter a name in the Name box, I
choose MyList
- enter the following formula in the Refers to box
=OFFSET($Z$50,,,COUNTA($Z$50:$Z$100))
- Choose Data, Validation, Allow, List and in the Source box enter
=MyList
3. Use VBA - sample not include here. Advantage you don't need to
predefine the range where the list may appear as in the OFFSET function.
You can specify as large a range as you want in the OFFSET formula, but
remember this range is dedicated to the list. This also means you should
put the new entries directly below the current list, if you skip a space
things will not work well.
Cheers,
Shane
"nip" <gbreath@gmail.com> wrote in message
news:8dce6c59-ac94-4087-859d-fa7eadbf6333@q27g2000prf.googlegroups.com...
| Quote: | Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible? |
|
| |
| | | nip | |
| | Posted: Tue Jul 08, 2008 9:10 pm Post subject: Re: drop down list | |
| | | |
On Jul 4, 6:46 am, "Shane Devenshire" <shanedevensh...@sbcglobal.net>
wrote:
| Quote: | Hi,
Three ways at least to do this:
1. Insert the new item between previous entries.
2. Use a dynamic range name:
- choose Formulas, Define Name and enter a name in the Name box, I
choose MyList
- enter the following formula in the Refers to box
=OFFSET($Z$50,,,COUNTA($Z$50:$Z$100))
- Choose Data, Validation, Allow, List and in the Source box enter
=MyList
3. Use VBA - sample not include here. Advantage you don't need to
predefine the range where the list may appear as in the OFFSET function.
You can specify as large a range as you want in the OFFSET formula, but
remember this range is dedicated to the list. This also means you should
put the new entries directly below the current list, if you skip a space
things will not work well.
Cheers,
Shane
"nip" <gbre...@gmail.com> wrote in message
news:8dce6c59-ac94-4087-859d-fa7eadbf6333@q27g2000prf.googlegroups.com...
Is there a way to create a drop down list in a cell which can be added
to? Excel 2007
Currently have a worksheet with a list in cells z50>z55 with entries
Then selected g5 to 50 and data validate > list > named range z50>55
Works well except when there is a change to one of the figures in the
list
Other than picking from this list no other entry can be made in g 5
50
So would like to be able to add other numbers to the list and have it
grow
Is this possible?- Hide quoted text -
- Show quoted text -
|
Thanks I will give it a try... can you give an example of the VB
code you would use... thanks |
| |
|
|
