Using validation to block cells

[RiotLoadTime]

I'm just getting started with using validation, so I know the gist o
using data->validation to limit the input perameters, but I wa
wondering if anyone could help me with something a bit mor
complicated. In one cell, I want to limit the user to inputting eithe
a 1, 2, 3, or N/A. If they put a 1,2, or 3, they can continue acros
the row, filling out the rest of the fields (patient data). If the
put an N/A, however, I'd like to somehow block them from entering an
data in the cells to the right of the cell with the N/A. I'm sur
excel can do this, I just couldn't find a tutorial about it.

Also, are there multiple types of "blocked" cells (i.e. locked, hidden
darkened out, input error if anything is typed in it, etc.)? Is there
way to do validation with each of them, or does validation only suppor
one type?

Thanks,
-RiotLoadTim

 

[raypayette]

Make a list let's say in Column D:
1
2
3
N/A

In the validation form select List and in the Source specify
=$D$1:$D$4

 

[starguy]

select your column in which you want to allow values 1,2,3,N/A
suppose you want to input these values in col A (range A1:A50)
select range A1:A50 and go to Data > Validation from Allow select Lis
and in Source put "1, 2, 3, N/A" (without quotes) and press Ok.
now select the range at the right of this column where you want to pu
other data based on the value in col A (i suppose that range i
B1:F50), after selecting this range go to Data > Validation, in Allo
select Custom, in Formula put =OR($A1=1,$A1=2,$A1=3) and press Ok.
now try to put data in your sheet and check for all validations.

hope this would serve your purpose.

 

[RiotLoadTime]

Ray,

I understand that that will make my drop-down box with those fou
options (1, 2, 3, N/A), but with it block the input user from fillin
in any of the cells in the same row if "N/A" is selected?

Thanks,
-RiotLoadTim

 

[starguy]

I have provided a solution for this, please look at my previous post.

 

[RiotLoadTime]

Thanks a lot Starguy, that helps quite a bit. I was wondering if you
also knew how to add additional validation to the cells in B:F so that
it's not only OR($AB3=1,$AB3=2,$AB3=3) but also the input user must
input a 0 or a 1. I tried =AND(OR($AB3=1,$AB3=2,$AB3=3),OR(0,1)), but
I obviously don't exactly know how the source field works.

Thanks again,
-RiotLoadTime

 

[starguy]

put this in formula if you want to allow 0 as well.

OR($AB3=0,$AB3=1,$AB3=2,$AB3=3)

 

[RiotLoadTime]

Starguy,

Sorry, I meant allowing the user to only input a 0 or 1 -in the cell
B:F-.

So in all: a user can only input a value into cells B:F if th
corresponding value in column A is a 1,2,3. Also, the value they pu
in B:F can only be a 0 or 1

Thanks,
-RiotLoadTim

 

[starguy]

hello try this.
select your range at the right of your column in which you have allowe
values 1,2,3,N/A
go to menu Data > Validation > under Allow select Custom and put thi
formula.

AND(OR($AB3=1,$AB3=2,$AB3=3),OR(AC=0,AC=1))

hopefully this would solve your problem.