Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turning Validation on/off on a series of cells
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(C10,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turning N/A cells to blank Cells | Excel Worksheet Functions | |||
turning N/A cells to blank Cells | Excel Worksheet Functions | |||
turning a string of cells into a work shift (eg. 9am-7pm) | Excel Worksheet Functions | |||
auto-expansion of excel cells, turning it off | Excel Discussion (Misc queries) | |||
VB - turning cells specified colours | Excel Programming |