I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub


Be sure to put N/A in your validation list - the macro bypasses it, but
otherwise the user will wonder why s/he can't directly enter it.


In article ,
Neil Goldwasser <Neil wrote:

Hi! I have a worksheet and I would like to use a "part-time" drop-down list.
What I mean is that cell B1 will look at cell A1. If cell A1 shows "N/A",
then B1 will automatically display "N/A" as well. If A1 shows anything other
than "N/A", i.e. some useful information has been added, then B1 becomes a
defined drop-down list. (I have already made and named the validation list).

I know it might sound lazy, but my company wants this worksheet to be as
automatic as possible, so instead of typing "N/A" into both A1 and B1, the
user will just need to type if in once (in A1).

Is there a way of doing this?

I had hoped there might be a formula along the lines of
=IF(A1="N/A","N/A", {{validation as explained}})

Maybe I'm asking too much, but if anybody knows how to do it I'd be very
grateful!
Cheers, Neil