Thread: Multiple validation problem

I'm working a sheet which we use at work (a restaurant) to record our reservation.

I have provided an example sheet to explain what I'm tyring to do.

If a guest wanted to book a table at 5.30pm for 2 people, I would enter a '2' into B4 (or whaterever time they wanted/free table). I have then set up a conditional format to shade a pre-defined number of boxes to signify how long that particular table will be booked for. (as you can see from my example text in L11 (shading done manually however as I haven't finished the conditional formatting for the entire sheet)

The problem I have is that I have set validation rules for the tables so that they cannot be booked over and above a set number of cover. The problem I'm encountering is that in the next cell along from the inserted number I would write the customer details (again see M11) but because this text is not defined in the validation rules it through back an error.

Is there a way to set two type of validation rules for one cell?
What I would need is to validate the numerical data inserted, but also allow for any text to be typed without the eroor showing.

Hi

You don't say what version of excel you are using, but I don't believe it can be done in 2003. Don't know about 2007.

If you are using 2003, then perhaps you should use an event macro. This way, the type of data in the cell can be evaluated (numeric or text) and you can set your tests accordingly.

As you have a set number of hours for each booking, you could use conditional formatting to work out the shading.

HTH

rylo

Originally Posted by rylo

Hi

You don't say what version of excel you are using, but I don't believe it can be done in 2003. Don't know about 2007.

If you are using 2003, then perhaps you should use an event macro. This way, the type of data in the cell can be evaluated (numeric or text) and you can set your tests accordingly.

As you have a set number of hours for each booking, you could use conditional formatting to work out the shading.

HTH

rylo

Hi Rylo,

thanks for replying.
I'm using 2007 to create the sheets, but then saving as 2003 as that's what we have at work.

I already have the conditional formatting set up to deal with the shading, but could you give me an example of the event macro you talk of as I'm afraid I don't know what this is.

Cheers

Hi

Have a look at the attached. I've put in a new sample sheet (14th eve rylo) and done some work on the range L11:S11.

If you put in a blank or 0 in L11, nothing happens. There are 3 formats available. 1-8 gives a yellow, 9-10 gives a green and > 10 gives red. There is also a message that pops up if a number > 10 is entered. If you then put some details into M11, it doesn't cause a problem, but the conditional formatting still works.

HTH

rylo

Hi Rylo,

Thanks for the attached file. This is almost what I'm looking for.

I'm afraid I know nothing of VB so you're going to have to walk me through this.

What I need to do is set a maximum value for certain tables.

ie. booths 1-3 (A4-A6) cannot have a tbale bigger than 15, but booths 4&5 (A7,A8) cannot be any larger than 9)

How would I set sepecific valiation vaules for certain tables numbers using your example.

Also it is possible to have a warning rather than a stop message as some table sizes can be overbooked by a member of management.

Thanks for your help so far.

Simon.