In sheet 1, A1:A100, I have a list of serial numbers. The rest of the sheet
contains various information
In sheet 2 A1, I have a drop down list (using A1:A100) and then VLOOKUP's to
pull information from sheet 1.

So far so good

Is there a way of eliminating the drop down and populating the cell in sheet
2 by just clicking on the required number in sheet 1 A1:A100 ?

I know a hyperlink can take me to sheet 2 but not the value of the
hyperlinked cell

Any ideas?

TIA

Anthony,

You could use the worksheet's selection - change event: Copy this code, and
right click the sheet tab, select "View Code", and paste into the window
that appears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet2").Range("A1").Value = Target.Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Anthony Slater" wrote in message
...
In sheet 1, A1:A100, I have a list of serial numbers. The rest of the
sheet
contains various information
In sheet 2 A1, I have a drop down list (using A1:A100) and then VLOOKUP's
to
pull information from sheet 1.

So far so good

Is there a way of eliminating the drop down and populating the cell in
sheet
2 by just clicking on the required number in sheet 1 A1:A100 ?

I know a hyperlink can take me to sheet 2 but not the value of the
hyperlinked cell

Any ideas?

TIA

The short answer is 'Yes, using VBA.'

The long answer is that you'll need to be more explicit in describing your
goals if we're to be able to give you concise guidance.

How do you determine where on the 2d sheet the data will go?
How do you determine what cells to copy into the 2d sheet?
Is there any other important factor you haven't stated?




"Anthony Slater" wrote:

In sheet 1, A1:A100, I have a list of serial numbers. The rest of the sheet
contains various information
In sheet 2 A1, I have a drop down list (using A1:A100) and then VLOOKUP's to
pull information from sheet 1.

So far so good

Is there a way of eliminating the drop down and populating the cell in sheet
2 by just clicking on the required number in sheet 1 A1:A100 ?

I know a hyperlink can take me to sheet 2 but not the value of the
hyperlinked cell

Any ideas?

TIA

Bernie

Thanks for that. That piece of code worked a treat

@Duke
I was going to reply to your post but Bernie answered and gave me the
solution i was looking for. Thank anyway for your support

"Bernie Deitrick" wrote:

Anthony,

You could use the worksheet's selection - change event: Copy this code, and
right click the sheet tab, select "View Code", and paste into the window
that appears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet2").Range("A1").Value = Target.Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Anthony Slater" wrote in message
...
In sheet 1, A1:A100, I have a list of serial numbers. The rest of the
sheet
contains various information
In sheet 2 A1, I have a drop down list (using A1:A100) and then VLOOKUP's
to
pull information from sheet 1.

So far so good

Is there a way of eliminating the drop down and populating the cell in
sheet
2 by just clicking on the required number in sheet 1 A1:A100 ?

I know a hyperlink can take me to sheet 2 but not the value of the
hyperlinked cell

Any ideas?

TIA