Using validation to block cells

[RiotLoadTime]

I'm just getting started with using validation, so I know the gist of using data->validation to limit the input perameters, but I was wondering if anyone could help me with something a bit more complicated. In one cell, I want to limit the user to inputting either a 1, 2, 3, or N/A. If they put a 1,2, or 3, they can continue across the row, filling out the rest of the fields (patient data). If they put an N/A, however, I'd like to somehow block them from entering any data in the cells to the right of the cell with the N/A. I'm sure 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 a way to do validation with each of them, or does validation only support one type?

Thanks,
-RiotLoadTime

[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

[RiotLoadTime]

Ray,

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

Thanks,
-RiotLoadTime

[starguy]

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

Ray,

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

Thanks,
-RiotLoadTime

[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)

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

[RiotLoadTime]

Starguy,

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

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

Thanks,
-RiotLoadTime

[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 List 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 put other data based on the value in col A (i suppose that range is B1:F50), after selecting this range go to Data > Validation, in Allow select Custom, in Formula put =OR($A1=1,$A1=2,$A1=3) and press Ok. this formula will restrict data entry in the right cells if col A cell has value other than 1,2,3. and if you want to restrict data entry in right cells if the cell in col A is blank then uncheck the option of Ignore blank in data validation custom view.
now try to put data in your sheet and check for all validations.

hope this would serve your purpose.

I'm just getting started with using validation, so I know the gist of using data->validation to limit the input perameters, but I was wondering if anyone could help me with something a bit more complicated. In one cell, I want to limit the user to inputting either a 1, 2, 3, or N/A. If they put a 1,2, or 3, they can continue across the row, filling out the rest of the fields (patient data). If they put an N/A, however, I'd like to somehow block them from entering any data in the cells to the right of the cell with the N/A. I'm sure 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 a way to do validation with each of them, or does validation only support one type?

Thanks,
-RiotLoadTime