drop down list

  • Thread starter Thread starter nip
  • Start date Start date
N

nip

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






- Show quoted text -
Click to expand...

Thanks I will give it a try... can you give an example of the VB
code you would use... thanks
 
Back
Top