Change input range of 50+ drop downs

**Kazstankgardner** · 06-17-2012, 11:17 PM

Hi All,

I am working on a workbook which has over 100 worksheets. In each of these worksheets, there is 12 form control drop downs, which I need to change the input range for (the input range is that same for each drop down). Does anyone know if there is a way to change the input range of all of the drop downs at once, rather than me spending hours and hours changing them one by one???

Thanks in advance.

**outofthehat** · 06-18-2012, 07:52 AM

Provided that the dropdowns are in the same cell on each worksheet, you can select all worksheet, then whatever you do affects every worksheet.

Click on the tab for the first worksheet. Shift-click on the tab for the last worksheet, and all worksheets in between are also selected. Now, ANYTHING you do to the worksheet you can see will affect all the worksheets.

**Kazstankgardner** · 06-19-2012, 12:59 AM

Hi & thank you for your response.

I have tried to change the input range with the method you have described above, without success. I am unable to click on the dropdown, at all, to make any changes. Any other suggestions??

Thanks again for your response.

kazsta

**outofthehat** · 06-19-2012, 07:54 AM

Ah, hadn't clicked that you can't add data validation across multiple sheets like that.

Right. You need to set up data validation so that the reference contains the sheet name on which the list appears. You can't do that in Excel 2003.... except you can. First, name the range containing the validation list, by highlighting it, typing the name you want into the Name box (next to the formula bar; contains the cell reference; don't uses spaces or any character that will confuse a formula) and hitting enter. Look at the dropdown on the Name box, and if you've named it successfully, it'll appear in that dropdown list. I named mine ValidationList.

In your data validation settings on Sheet1, change it to read =<list name>, e.g. =ValidationList. The = sign is essential.
Now, copy that cell.
Select all the other sheets, as we did before (click first tab, shift-click last tab).
Paste into the cell you want the validation on, and you should find it pastes the validation into that cell on every sheet.