DropDown lists and validation help

**cappy95833** · 07-01-2009, 05:51 AM

I have the following fields: colA & colB
the total of the two columns must equal 5 or less.

because I want to restrict the entry into these to columns, is there a way that I can have colA have a dropdown list of 1 through 5 and then when someone selects 2, colB will only show 1 to 3 available?

**zbor** · 07-01-2009, 07:21 AM

Try to look this link.

http://www.contextures.com/xlDataVal02.html

**sweep** · 07-01-2009, 07:31 AM

Or you can enter in a list of cells (for example K1:K5) 1,2,3,4,5

Define the range as a named range (for example "options")

Presuming your 1st set of data is in column A, select column B and define the custom validation like so:

=OFFSET(options,0,0,5-$A1,1)

**kimbal** · 07-01-2009, 07:36 AM

Hi Cappy,
Please check the attached excel,
Sheet1:
A1: Col A
B1: Col B
Sheet2:
A1:A5 a list of item,visible according to entry in col B1 of Sheet 1
b1:B5 a list of item,visible according to entry in col A1 of Sheet 1

**martindwilson** · 07-01-2009, 07:48 AM

if you're just limiting a1 against b1 then a2 aginst b2 and so on
then in a1 use data/validation/custom and put
=OR($A1+$B1<5,$A1+$B1=5) in the formula box
trhen click on input message and error alert tabs an put in some suitable message on each
click ok
copy/paste special "validation" to to cells in range A1:B?
now you wont be able to put in a total that is >5
(note as with any validation if someone pastes into cell it will override validation any way)