Excel: Automatic Field Entry 1

[bdc138]

Here is the basic idea....

A
7 N
8
9
10
11
12

The possible values to be entered are N,S,E,W. I want to create a formula that when A8 is highlighted it enters the appropriate value(N,S,E,W) into A8. In this case N. When A9 is highlighted it would automatically enter N. If the user were to change the value of A9 to S, then when A10 was highlighted "S" would automatically be entered...and so on...and so on....

Any help would be greatly appreciated.

P.S. To Zathrus...Thanks for the last reply you sent about formatting columns of data into rows. I needed to make some slight modifications but the code was a great help

Thanks,
Brent

 

[RobBroekhuis]

Use worksheet events, something like:

dim CurrentDirection as string

sub worksheet_selectionchange
if target.column=1 and target="" then '(an empty cell in column A)
target=CurrentDirection
end if
end sub

sub worksheet_change
if target.column=1 and instr("NSEW",target)>0 then
CurrentDirection=target
end if
end sub

If you need help with inserting worksheet event code, let us know.
Rob