Help appreciated

[chef]

Hi,

Would welcome some help on, what I think is needed is VBA, of which I have very limited knowledge.

I have rows of staff numbers on column A in sheet1

eg
T123456
T123457
T123458

I want to be able to click on any staff number in column A and then display that chosen staff number in cell A1 in sheet2, in order i can then perform a lookup on that staff record.

any help greatly appreciated
regards
chef

 

[VoG]

Try this: right click Sheet1's tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Sheets("Sheet2").Range("A1").Value = Target.Value
End Sub

 

[chef]

Peter,

Abs brilliant....saves a whole pile of work.

Thanks for the quick reply and help

regards
Ian

 

[chef]

Peter,

Can I also ask its its possible to click on any staff number and automatically go to sheet 2?

regards

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    With Sheets("Sheet2")
        .Range("A1").Value = Target.Value
        .Select
    End With
End If
End Sub

 

[chef]

Peter,

BRILLIANT !

thanks again.

Ian

 

[chef]

Hi,

I am really sorry to bump this up again but this has saved my dept sooo much work.

Next thing I would like to do, if possible, is:-

once i go to chosen record on sheet2 i can display staff absence record etc....can i then click on text with "Staff Record" (cell g1,sheet 2) or a button on sheet 2 to automatically open up a word document with that member of staff's personal file based on staff record number?... I would reference and save the word document in a folder g/public/chef/staffrec

and save staff records eg T123456.doc etc

hope this makes sense..and achievable. I'm trying to open up an individuals staff record by clicking on a cell in excel and open associated word file.

regards
ian

 

[VoG]

Sorry, opening Word from Excel is not my forte. Try starting a new thread.