Revised Dropdown list not applying to some rows
 

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

Revised Dropdown list not applying to some rows
 

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

"Ashutosh" wrote:

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

Revised Dropdown list not applying to some rows
 

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

Ashutosh wrote:
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 ?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Revised Dropdown list not applying to some rows
 

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


"Debra Dalgleish" wrote:

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

Ashutosh wrote:
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 ?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Revised Dropdown list not applying to some rows
 

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.
--
Ashutosh


"Barb Reinhardt" wrote:

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

"Ashutosh" wrote:

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

Revised Dropdown list not applying to some rows
 

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

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html