Drop Down--How do I copy and update cell link/input range

[Guest]

I am using a spreadsheet created by someone else some time ago. It contains
drop down boxes. I'm not familiar with forms and drop down boxes, etc. When
I right click on each drop down, and click Format Control, I want to revise
the Cell Link to be the row I'm on, but I also want to update the Input Range
to be a different sheet within the same workbook. Does this make sense?? If
I try to copy the box with the Drop Down by arrowing over onto that cell, and
doing Ctrl+C, then moving to the destination and doing Ctrl+V, it just copies
the same Cell Link and Input Range. The only way I can see to change either
or both of these is to right click every Drop Down box, choose Format Control
and change each one manually. I have 60 rows and 17 columns, this could take
hours!! HELP.

 

[Guest]

Mary
Dropdown boxes are normally used in Lookup Formulas - if this is the case
you might want to use Data Validation with lists. Visit www.contextures.com
and look for this subject in Excel Tips.

Also have a look at Filters and Autofilters on the same site, and Excel Help.

With the controls you describe AFAIK you are right, format each control and
set the lookup range. e.g Sheet2!A1:A200. It might be better to name the
ranges as these include the sheet name when you create them. If the range is
likely to change in the future (have items added at the bottom of the list)
Debra Dalgliesh at contextures.com also has wonderful stuff on Named Ranges.

It is always awkward taking over someones designs, especially if they have
left no notes but examine what you need and you can maybe improve the
original design. There are plenty of people to help you with specific
questions.

Regards
Peter.