dependent data validation problem

[merlin777]

<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 451.3pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=602><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 451.3pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=602 noWrap>as part of a roster schedule I have 2 adjacent cells (per job, per day). In one I put the name of the member of staff or freelancer on duty, in the other goes the type of cover used (overtime, a shift swap, left uncovered or a freelancer).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 451.3pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=602 noWrap>I'm using data validation pulldowns in both.<o></o>

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 451.3pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=602 noWrap>Here's my problem: the list with the names might have over 150 names because it includes staff and freelancers. That a bit of a pain to scroll through.<o></o>

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 451.3pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=602 noWrap>Ideally I'd like to automatically offer a list of either staff or freelancers to halve the length of the list in the pull down.


</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 451.3pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=602 noWrap>I've looked at dependent data vaildation lists but they don't work because the cover-type cell is either 'freelancer' or a range of other types of staff cover.
<o>any thoughts?</o>
<o></o>
VB isn't an easy option because the workbook has to be heavily shared in a high (macro) security environment.
</TD></TR></TBODY></TABLE>

 

[merlin777]

I'm an idiot. Here's mysolution (although I think it's a bit clever):

=IF(OR(F30=E30,F30=E31,F30=E32,F30=E33,F30=E34,F30=E35,F30=E35,F30=E36),STAFF,FRLNCR)

where cells E30:E36 contain the staff cover types. Logical OR being less ponerous than multiple nested IFs.

Of course your solution has the benefit of common sense!

The only problem is that if someone overtypes the cell with a misspelled 'FRLNCR' it'll default to the staff list but I'll find a way around that...

Thanks everyone! Learned a lot from this!

 

[T. Valko]

I'm an idiot. Here's mysolution (although I think it's a bit clever):

=IF(OR(F30=E30,F30=E31,F30=E32,F30=E33,F30=E34,F30=E35,F30=E35,F30=E36),STAFF,FRLNCR)

where cells E30:E36 contain the staff cover types. Logical OR being less ponerous than multiple nested IFs.

Of course your solution has the benefit of common sense!

The only problem is that if someone overtypes the cell with a misspelled 'FRLNCR' it'll default to the staff list but I'll find a way around that...

Thanks everyone! Learned a lot from this!

You can reduce that formula down to:

=IF(OR(F30=E30:E36),STAFF,FRLNCR)

 

[merlin777]

magic - thanks

 

[T. Valko]

magic - thanks

You're welcome!