Lists

  • Thread starter Thread starter Kevin McClement
  • Start date Start date
K

Kevin McClement

Hello again!

This is my last visit to the newsgroup today.. hopefully.
When assigning Data Validation to a cell and then
designating a list to validate from is there any way to
have the list update with new entries if you haven't
designated space for it in the list formula?
LIST in Col Z:
apples
oranges
grapes

Data Validation formula in target cell(s)
=$Z$1:$Z$3

I'm asking just in case someone would need to have data
somewhere under the list and therefore you couldn't use
$Z:$Z for the list. Hope this is clear enough..

TIA
Kevin McClement
 
Use a dynamic range and name the list, i.e.

assume the list starts inA1, insert>name define and give it a name, in the
reference box put

=OFFSET($A$1,,,COUNTA($A:$A),)

click OK.

now in the validation use allow>list and

=MyList

(replace MyList with the name you gave the list)
 
You can use a dynamic formula to define the range. There are some
instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

However, if you want to store other data in the same column, below the
list, don't use the $Z:$Z reference. Instead, use the maximum space that
you know is available, e.g. --

=OFFSET(Sheet1!$Z$1,0,0,COUNTA(Sheet1!$Z1:$Z50),1)

where the other data will start in row 51.
 
Thanks to Deb and Peo.. I usually think i'm pretty good
with Excel.. then of course i find someone better!

Thanks again!
Kevin McClement
 
Being a lazy sort of guy, all I do to increase the length of my list is
simply insert a row, which automatically expands the selection.

Knowing you're lazy ahead of time really helps, because I always place all
of my lists in a location where row insertion is not objectionable.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks to Deb and Peo.. I usually think i'm pretty good
with Excel.. then of course i find someone better!

Thanks again!
Kevin McClement
 
Back
Top