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


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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.
now try to put data in your sheet and check for all validations.

hope this would serve your purpose.

RiotLoadTime Wrote:
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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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

RiotLoadTime Wrote:
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
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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

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

RiotLoadTime Wrote:
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
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=569856

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

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

hopefully this would solve your problem.

RiotLoadTime Wrote:
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
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=569856