Data Validation or conditional formating

**benswelding** · 06-04-2011, 08:34 AM

Hello.I have a spreadsheet that I enter numbers in cells N 13 to N54 What im trying to do is put data validation in that column that will only allow user to enter the number 1 and nothing greater if the adjacent cell L 13 to L54 contains the word day. I would like it to either not let them enter number or give them a warning message..Any help would be appreciated.
Thanks in advance

**MarvinP** · 06-04-2011, 09:44 AM

Hi benswelding,

If you want to show a message and test what is being entered I believe you need some Event VBA. Code like the following will get you started.

Please Login or Register  to view this content.

See the attached to give it a try. Type something in the yellow area and the worksheet event macro will fire. You will need to be more specific on what is allowed for us to do much more.

**benswelding** · 06-04-2011, 10:35 AM

Thanks for your help and sorry I didn't explain better.I dont want user to be able to input anything but a 1 in cells n13 to n54 if cells l13 to l54 contains the text day.Thanks for your help.

**snb** · 06-04-2011, 10:40 AM

Datavalidation in N13

PHP Code:


=OR(L13&N13="day1";L13<>"day")

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

Ok - try this attached with the code of

Please Login or Register  to view this content.

**benswelding** · 06-04-2011, 10:08 PM

Thanks for your help and I can see how my wording confused you (eg ) if L13 contains the text day then n13 can only contain the value 1 if L13 doesn't contain day then the value in cell n13 can contain any value,and I want this to happen through cells n13 to n54 and L13 to L54.Sorry again for not explaining this very well.Thanks for your help

**MarvinP** · 06-04-2011, 10:34 PM

Hi,
The last attached file should work as you want. It is always easier to understand the problem with a sample file.

If the problem is solved you can edit the origional (first) thread and prefix it with [Solved]. That helps us not try to solve already solved questions.

I hope you got the answer you needed.

**benswelding** · 06-05-2011, 01:45 PM

The problem with your sample sheet is if the text day is in any cell in column L..I can only enter a 1 in the cells in column n.I would only want the macro to change the adjacent cell.(eg)
if L13 = Day then n13 would have to be a 1.If l13 did not have the text day n 13 could have any value.Thanks in advance