working with data validations

**Daanish** · 02-10-2012, 12:51 PM

i have a template i've created for users. in this they need to choose a group from a dropdown list (grp100, grp200...grp900). i'd like the user to be able to extend this list as well though, from changing the source of these data validations. the problem is i dont know how many groups they'd like to add. right now this dropdown is looking at, say, A1-A9 for its source. is there some way to instruct it to start at A1 and go down as far as it has content?

also in the next column i'd like to add descriptions for these groups. right now there's none.
after choosing the desired group i want a separate cell to output the Group then the desc.

Eg in col A we have the source data - A1='Grp100', A2 = 'Grp200' etc.
in B1 I add "the first group", B2 = "the second group" etc.

in A50 I have my dropdowns. User chooses A3.
in B50 I'd like to display 'Grp300{The Third Group'

Q1 - how do i change the validations of the dropdown so that the list can be increased by the user
Q2 - how would I extract the desc?

thanks

**NBVC** · 02-10-2012, 01:28 PM

Create a Dynamic Named Range

**Daanish** · 02-10-2012, 02:02 PM

thanks man.

so it seems the formula is: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

the thing is i dont want to use A:A as a parameter because I would most likely have other stuff in the column, tho not in the immediate area. i might have content say 20 cells above.

what does 'offset' mean? the explanation on that page says rows to offset / columns to offset = 0, not sure what that is supposed to mean.

**NBVC** · 02-10-2012, 02:08 PM

You can limit the range to A1:A20

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$20),1)

this will then only count occupied cells in A1:A20.

the formula offsets from A1 by 0 rows and 0 columns.(so starts at A1), and then adjusts its range height to the number of cells counted in COUNTA() function and one column wide.