can't do wholesale data validation edits

jan001

Board Regular
Joined
Jul 22, 2004
Messages
123
I have a timesheet built for a particular employee. It includes a dropdown column of projects for him to select from, using a column of list boxes. Management has just sent me a list of projects to be added to the list.

It seems like I should be able to edit the list once, by highlighting each tab and the making the changes "wholesale", but it won't let me. It will let me open the reference list and edit it, but then when I try to adjust the data validation properties to reflect the longer list, "Data Validation" is grayed out. The only way I can do it is one sheet at a time.

FWIW, I'm using Excel '10 but have the document saved as Excel 97-03 at management's request.

Any help will be much appreciated, as always!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Right, Validation can't be edited in grouped sheets.

As for the list itself, it's probably easiest if you use a named range instead of actual cell references. That way you don't have to worry about size. And instead of editing all validation cells, if they're all the same, just fix one, then copy that cell and paste it wherever else you need it. The validation will go with the copy.

HTH,
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
If all the sheets that are using the list are referring to the same Data, makes more sense to have just one list, in a named range, then all the sheets can refer to it. Then it will only ever be the one list that you update
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top