How to data validation in multiple sheets at a time

**ibanez7** · 06-12-2011, 03:16 PM

Hello everyone.

Is there a way to configure data validation in multiple sheets in excel 2007 at a time?
When I try to configure data validation in multiple sheets at a time it's greyed out so if won't work. Is there a code to make this work? Do I need to create a macro with code in it?

Thanks for any help with this.

**MarvinP** · 06-12-2011, 03:23 PM

Hi ibanez7

I don't know what you are asking when you say "data validation in multiple sheets at a time".
Data Validation applies to a single cell.

That said - You can select multiple sheets by control clicking on the tabs. When you have multiple sheets selected the Data Validation Icon is greyed out. Selecting multiple sheets is not recommended because we forget multiple sheets are selected and overwrite stuff.

**ibanez7** · 06-12-2011, 03:52 PM

Hello and thanks for your reply.
What I mean is I created multiple sheets for machines that are either: Up , Down or Idle. I got the formula in to calculate the Idle time as:

Idle:
=10.5-(F14+G14)
=10.5-(F15+G15)
=10.5-(F16+G16)for each cell depending on the cell it is in and type of machine.

Now I want to make sure that the Sum of Up+Down will not exceed 10.5hours(which is = to the idle time specified since this is the total hours in a shift). So the formula in the data validation is:
Custom (data validation)
then: =SUM(F15:G15)<=10.5
Now I need to enter this in each cell for all the sheets created for example the months: June , July, August etc... for entire year. This is why I am wondering if I can create a macro with specific code to enter this in one cell example: F15 and would automatically enter the same in all of July since all of my sheets are created exactly the same.

Example: F15 in July first is all the same for August, September, October etc..

My problem is if I choose all the months (different sheets) with ctrl to highlight all of them the data validation is greyed out so I can't enter the same data in all of the cells in 1 shot. Know what I mean?
Is there a way to do this?

Thanks for the help

**ibanez7** · 06-12-2011, 04:04 PM

I think the easiest way is to create the first sheet then hold control and copy it in all other sheets. The reason I was asking is because I already had the other sheets done in all of the months.

Just like you mentioned above:
That said - You can select multiple sheets by control clicking on the tabs.
Then just copy the first one into all of the other ones that way the data validation seems to be carried over to all other sheets this way. Just too bad cause that's all that is missing in the other sheets.
Unless you have a better way of doing this.

Thanks again

**ibanez7** · 06-12-2011, 04:06 PM

I think the easiest way is to create the first sheet then hold control and copy it in all other sheets. The reason I was asking is because I already had the other sheets done in all of the months.

Just like you mentioned above:

"That said - You can select multiple sheets by control clicking on the tabs."

Then just copy the first sheet into all of the other sheets that way the data validation seems to be carried over to all other sheets this way. Just too bad cause that's all that is missing in the other sheets.
Unless you have a better way of doing this.

Thanks again[/QUOTE]

**MarvinP** · 06-12-2011, 04:08 PM

Hi ibanez7,

I almost gave some dumb answer, imagining what you workbook looks like. To give you a good answer we need a sample workbook with some discription of what you mean.

You can attach a sample workbook by clicking on "Go Advanced" below the message area and then on the Paper Clip Icon above the Advanced Message Area.

**ibanez7** · 06-14-2011, 06:05 PM

Thanks very much for your help...I did it the way I mentioned above and didn't take too long...thanks again

**ibanez7** · 06-14-2011, 06:08 PM

Is there a way I can make absolutely sure that a user inserts a number value and not a letter or x as a check mark in a cell? (Microsoft excel 2007)

I noticed that if a user inserts a letter for example it will screw up the sheets and formulas won't work.

Thanks for the help.