Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
Applying auto-filter with large number of rows | Excel Discussion (Misc queries) | |||
Applying formulas only to the subtotals of a data list | Excel Discussion (Misc queries) | |||
Number Applying Value To Text Selection In List Box | New Users to Excel |