Validation List Query

[SamuelT]

Hi all,

I've got a validation list showing a project problem status: New,
Resolved, In Progress. These options are a named range 'Status'.

Thus I do Data>Validation>Allow:List>Source:=Status

No problem there. However, from time to time I need to input a custom
status, which could be anything. The problem being that once I have a
cell validated it does not allow me to type anything in it. Is there a
means that keeps the validation drop down, but also lets me input my
own data if, and when, I need to?

I could always remove the validation from the individual cells, but
hope someone might have a slightly less clunky solution.

TIA,

SamuelT

 

[Bob Phillips]

Samuel,

This may be way off-beam for you, but I will float it anyway.

Assuming that the DV is in cell G4.

Data>Validation>Allow:List>Source enter

=IF(F4="",OFFSET(Status,0,0,(COUNTA(Status)),1),F4)

Uncheck the Ignore blank box.

When you OK, this will throw up an error, but just OK it.

If F4 is blank, you will get you usual list.

If you enter the alternate value you want to use in F4, when you select the
dropdown next to the DV it will only show that new value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Coderre]

You could still use the same Data Validation if you do this:

Data>Validation
Allow: List
Source: =Status

Select the Error Alert tab
Style: Information
Title: New Item
Error Message: You entered an item that is not on the list.
Click the [OK] button

After doing that, if you enter a non-listed item...a message displays
to alert you, but the new value is still accepted.

Is that something you can work with?

Regards,
Ron

 

[SamuelT]

Thanks guys. Those both work really well!

SamuelT

 

[Bob Phillips]

Bit simpler than mine <vbg>

If you uncheck the Show error alert button, it is completely unobtrusive (no
validation of course, but hey you can't have everything).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ron Coderre" <[email protected]>
wrote in message
news:[email protected]...

 

[RagDyeR]

Simply increase the size of your "source" range to include an empty, blank
cell at the bottom, and you'll allow *any* user input.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message
Hi all,

I've got a validation list showing a project problem status: New,
Resolved, In Progress. These options are a named range 'Status'.

Thus I do Data>Validation>Allow:List>Source:=Status

No problem there. However, from time to time I need to input a custom
status, which could be anything. The problem being that once I have a
cell validated it does not allow me to type anything in it. Is there a
means that keeps the validation drop down, but also lets me input my
own data if, and when, I need to?

I could always remove the validation from the individual cells, but
hope someone might have a slightly less clunky solution.

TIA,

SamuelT