DEPENDENT VALIDATION

M

mellowe

Hi

Is there any way to validate a cell so that if 0 appears in it ( e.g
after a VLOOKUP ) that a drop down box is available for the user to
select an option from. I have my lists set up already but not sure how
to do this ... I am not sure if the custom box is what I need here?

Any suggestions welcome! thnx
 
M

mellowe

Thanks Debra I had seen this link before and have used it previously
for dependent lists and it was great, but just having no luck trying to
get this to work for me in this case as not sure how to put in a rule
that if the cell has a 0 in it then offer the user a drop down list - I
cant seem to tell it to do this! any ideas? thnx
 
D

Debra Dalgleish

If cell B3 contains a 0, you could use the following data validation in
cell D3:

For Allow, choose List
In the Source box, type:
=IF(B3="",NAList,IF($B$3=0,MonthList,NAList))

The NAList is a named cell with the entry "N/A"
 
M

mellowe

Thanks Debra had a go with this, buit still having problems. It may
help to know the full problem I have: ( its 2-fold actually)

Basically I need a LOOKUP formula that populates cell O7 on Sheet 2
with same value as that on Sheet1 cell O7 IF:

the cell value Sheet1 B7:B200=Sheet2 B7:B200
AND Sheet1 C7:C200=Sheet2 C7:B200
AND Sheet1 D7:D200=Sheet2 D7:B200
AND Sheet1 E7:E200=Sheet2 E7:B200

if all these values ARE NOT the same from Sheet 1 to Sheet 2 then cell
O7 is populated with "0" I would then want a drop down list to be
available with a list of options the user can choose from ( I have
already set up my list called: Area_responsible.)

My range is B7:p200 ( so not sure whether i would need relative or
absolute values in my range so that the whole range is checked for
example: Sheet 1 cell B9 might be 2677 but the same value is in cell
B11 on sheet 2 )

Please Please help as this is so frustrating!
 
Top