I am making a data-entry spreadsheet. I have a drop down menu for each
column, and I want to copy that drop down menu and paste it in each cell for
that column. When I have tried to follow instructions on excel (copy, paste
special, check validation), the top item in the menu is erased. It looks like
the range is changing in each cell (i.e. =F1:F6 in F16 goes to =F2:F7 in F17,
then =F3:F8 in F18). How can I copy my drop down menu and keep it the same
for each cell?

Thanks for your help!

How about an alternative?

Instead of using a dropdown from the Forms toolbar or a combobox from the
Control toolbox toolbar, you may be able to use data|validation.

Debra Dalgleish has lots of info:
http://contextures.com/xlDataVal01.html

Browniris wrote:

I am making a data-entry spreadsheet. I have a drop down menu for each
column, and I want to copy that drop down menu and paste it in each cell for
that column. When I have tried to follow instructions on excel (copy, paste
special, check validation), the top item in the menu is erased. It looks like
the range is changing in each cell (i.e. =F1:F6 in F16 goes to =F2:F7 in F17,
then =F3:F8 in F18). How can I copy my drop down menu and keep it the same
for each cell?

Thanks for your help!

--

Dave Peterson

Two ways:

1) Use a Named Range instead of cell references

Highlight your list of values, and type name into the name box (just to the
left of the formula bar)...something like OPTIONS

Then in the data validation formula, use List: =OPTIONS

Now when you copy it, the the range won't change

2) Use Absolute references

You're using =F1:F6
Change that to =$F$1:$F$6

That won't change when you copy it.


--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Browniris" wrote:

I am making a data-entry spreadsheet. I have a drop down menu for each
column, and I want to copy that drop down menu and paste it in each cell for
that column. When I have tried to follow instructions on excel (copy, paste
special, check validation), the top item in the menu is erased. It looks like
the range is changing in each cell (i.e. =F1:F6 in F16 goes to =F2:F7 in F17,
then =F3:F8 in F18). How can I copy my drop down menu and keep it the same
for each cell?

Thanks for your help!