Solved Drop down lists in Excel 2007

July 25, 2011 at 13:05:52
Specs: Windows XP
I have created a drop down list but need to repeat this same drop down list many times in a spreadsheet - I can't seem to copy it without losing content and it would take forever to repeat the data validation process for each cell. Any way to copy or repeat the initial drop down into successive cells down the column?

See More: Drop down lists in Excel 2007

Report •


✔ Best Answer
July 26, 2011 at 13:08:40
go into your defined name manager and try changing it from
=A3:A10

to

=$A$3:$A$10

then try copying it and see if you get the same problem. You may also have to tack on the sheet name to that.



#1
July 25, 2011 at 15:18:02
Why not select all of the cells and create the Data Validations for all of the cells at once?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 25, 2011 at 17:43:42
depending on the nature of how you defined your drop down list, you may have the option to copy the orginal cell with the data valadation then do a paste special with the validation option selected.

How did you generate the initial drop down list


Report •

#3
July 26, 2011 at 04:44:17
Neither of these work - tried it yesterday. Each successive cell has 1 less items, until it runs out then nothing - e.g. I have 9 items in list - second cell has 8, etc. until the cells are blank, even though there is a drop down arrow beside it?????

Report •

Related Solutions

#4
July 26, 2011 at 04:48:39
I have also tried creating several individual cell drop downs, then trying to copy them into the other file - same result.

Report •

#5
July 26, 2011 at 08:35:58
assuming the validation entries are in B2 to B4

are you using a list and saying

=B2:B4

or

=$B$2:$B$4

or

= list_of_values

where list_of_values is a named range defined as $B$2:$B$B4

or

all of the assumption is wrong and you manually typed the list ie:

validation word 1, Validation word 2, Validation word 3, Validation word 4


Report •

#6
July 26, 2011 at 11:00:30
I had the list of items in a column within the worksheet and named the location: =A3:A10.

Report •

#7
July 26, 2011 at 13:08:40
✔ Best Answer
go into your defined name manager and try changing it from
=A3:A10

to

=$A$3:$A$10

then try copying it and see if you get the same problem. You may also have to tack on the sheet name to that.


Report •

#8
July 26, 2011 at 13:17:00
Got it done. Made list on sheet 2, inserted it in the top cell of the column with data validation routine, then extended it to the rest of the cells in the column by aligning cursor with bottom right edge until I got a + cursor. Worked like a charm. Thanks for all your help.

Report •

Ask Question