Revised Dropdown list not applying to some rows

[Guest]

I revised my original dropdown list to include new data. I then tried to
apply it all cells with the same settings by checking the box in the Data
Validation dialog box. Most rows in the column got a blue autofill to show
that these were the rows to which the new dropdown list would get applied.
However, there were some rows (dispersed, not contiguous) which had no blue
autofill - however, these rows have data in them which has been derived from
the original dropdown list. How do I apply the new list to even these rows? I
could, obviously, Copy Paste from an 'applied' cell to an 'unapplied' cell,
but that means that I have to re-enter the data all over again. What should I
do ?

 

[Guest]

Why not create a named range for your dynamic list and in the validation list
(I assume that's what you're doing), enter =LISTNAME for the list. All
you'll need to do in the future is change the list.

You can dynamically change a list using offset. Lets say your list header
is in A1 and the list is in A2:A100 (all rows filled), and you can
potentially add to the length of the list. When you name the range use

=OFFSET(A1,1,0,COUNTA(A:A)-1,1)

HTH,
Barb Reinhardt

 

[Debra Dalgleish]

Copy a revised cell
Select cell(s) that you want to change
Choose Edit>Paste Special
Select Validation, and click OK

 

[Guest]

Thanks - it worked !! Just tells me that we need to read the menus more
carefully !!

 

[Guest]

Thanks, Barb - I think my problem was far simpler than it appeared - Debra D
gave me the answer - please see the other reply to my query.

 

[Debra Dalgleish]

You're welcome! And who has time to read all those menus? <g>