M
marston.gould
Two Validation Questions
1:
I have a worksheet where I'd like to have a column with a checkboxes on
it (is this advisable?). The purpose of this checkbox would be to allow
a user to decide whether to turn a series of validation routines on/off
for a particular row of cells. So I guess my first issue is - is it
possible to trigger a validation on a cell from a check box?
2:
The second issue is that on this worksheet a user will have the ability
to enter a series of 6 numbers over any number of rows (probably under
100 usually, 200 is probably max).
Upon entering (or selecting - not sure which method is best) here - the
choices in some of the other cells will probably be limited.
I know how to use columns of data and variable lengthed named ranges to
accomplish for a single cell, but not over several rows of those
numbers.
For instance on the user worksheet imagine:
CELL1 CELL2 CELL3 CELL4 CELL5 CELL6
Where CELL1-6 are the name of those cells all in one row.
On a second worksheet:
A B C
CELL1S CELL2S =sum(offset(C$2,1,0,counta($A:$A)-1,1))
aVal1 bVal1 =if(aVal1=CELL1,1,0)
aVal2 bVal2 =if(aVal2=CELL2,max(C$2:C2)+1,0)
.. .
.. .
.. .
aValn bValn .
(contining on to Col D)
D
=if(C1>0,1,"")
=if(D1<>"",if(D1+1=<C$1,D1+1,""))
..
..
..
(contining on to Col E)
E
=if(D1<>"",offset($B$2,match(D1,$C:$C,0),0,1,1)
..
..
..
Then I could set up a named range
CELLS2 = offset($E$2,0,0,$C$1,1)
Then apply this validation back on the user sheet to CELL2
My problem is, this only works on a single row - not a series of
rows...
Thoughts and suggestions...
Thanks in advance
1:
I have a worksheet where I'd like to have a column with a checkboxes on
it (is this advisable?). The purpose of this checkbox would be to allow
a user to decide whether to turn a series of validation routines on/off
for a particular row of cells. So I guess my first issue is - is it
possible to trigger a validation on a cell from a check box?
2:
The second issue is that on this worksheet a user will have the ability
to enter a series of 6 numbers over any number of rows (probably under
100 usually, 200 is probably max).
Upon entering (or selecting - not sure which method is best) here - the
choices in some of the other cells will probably be limited.
I know how to use columns of data and variable lengthed named ranges to
accomplish for a single cell, but not over several rows of those
numbers.
For instance on the user worksheet imagine:
CELL1 CELL2 CELL3 CELL4 CELL5 CELL6
Where CELL1-6 are the name of those cells all in one row.
On a second worksheet:
A B C
CELL1S CELL2S =sum(offset(C$2,1,0,counta($A:$A)-1,1))
aVal1 bVal1 =if(aVal1=CELL1,1,0)
aVal2 bVal2 =if(aVal2=CELL2,max(C$2:C2)+1,0)
.. .
.. .
.. .
aValn bValn .
(contining on to Col D)
D
=if(C1>0,1,"")
=if(D1<>"",if(D1+1=<C$1,D1+1,""))
..
..
..
(contining on to Col E)
E
=if(D1<>"",offset($B$2,match(D1,$C:$C,0),0,1,1)
..
..
..
Then I could set up a named range
CELLS2 = offset($E$2,0,0,$C$1,1)
Then apply this validation back on the user sheet to CELL2
My problem is, this only works on a single row - not a series of
rows...
Thoughts and suggestions...
Thanks in advance