Data Validation query

[hmltnangel]

Hey folks,

A little help would be welcome with this one.

Trying to put together a basic sheet to record complaints. Nothing too fancy but essentially I want it to do the following.

Lot of data capture cells to record who the complaint is for etc..... nice and easy part.

Next I wanted a list of 'Nature of Complaint' in a dropdown box. From that I wanted in the next column for a detailed nature of complaint catgeory that was dependant on the first dropdown box.

ie:

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18></TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2>G</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2>H</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2>I</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>
</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> Account Number</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> Nature of complaint</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> Detailed Category</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>7</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2></TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2></TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17> 8</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> </TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=128 colSpan=2> </TD></TR></TBODY></TABLE>
If cell H7 had a dropdown list then cell I7 would give a dropdown list that would differ depending on what you selected in cell H7.

I tried to follow this

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

but I seem to have a problem where cell I7 always displays the same dropdown no matter what is selected in H7.

Any helps or suggestions?

Is teher anyway of doing this that displays in I7 the full sentence rather than a single word?

 

[Andrew Poulsom]

What have you put in the Allow box for I7? What are the choices in H7, and what are your named ranges?

 

[hmltnangel]

Hi,

I put Allow - Lists in I7

Name ranges are

<TABLE style="WIDTH: 117pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=156 border=0 x:str><COLGROUP><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 117pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=156 height=18>BIB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>COL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>TELE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>BBACCOUNT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>PAYMENTS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>CURRENCY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>COMMERCIALOTHER</TD></TR></TBODY></TABLE>


Double checked all my item list names correspond with the category names too.

 

[Andrew Poulsom]

What did you put for the Source in I7?

 

[hmltnangel]

I puit

=INDIRECT($W$7)

W7 is the first cell in the Nature of complaints list

 

[Andrew Poulsom]

Doesn't it need to be =INDIRECT(H7) since that cell contains the dropdown that you want to treat as the precedent?

 

[hmltnangel]

Doesnt =INDIRECT(W7) refer to the dropdown in I7 being an indirect value of whatever is in the list starting at W7?

The webpage I was working from indicates that as well.

 

[hmltnangel]

You were right

sorry

all sorted with that part now.