Linking to an active cell

Hi,

I have a range of cells say from A1 to A20 having certain numbers. I
would like the output of any of these cell range in C1 depending on
what the active cell is. The active cell will always be one cell and
not a range of cells.

For eg, If the active cell is A5, the value of A5 should appear in C1.
As the person presses the down arrow and scroll downs the list, the
number in C1 should change accordingly.

The output in C1 is supposed to drive various Vlookup formulas.

If the active cell is no longer within the A1 to A20 range, the last
selected cell in A1 to A20 should remain in C1.

Appreciate anyone's help..

Thanks
Sandip.

This goes in worksheet code page, after you have entered a value if you move the cursor back to the cell the value is displayed in c1, i didnt have time to test it properly but it should work!

HTH

Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A1:A20")
For Each cell In rng
Range("C1").Value = ActiveCell.Value
Next

End Sub

Put this code into the worksheet module ... (right click worksheet tab
select View Code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 20 Then
Range("C1") = ActiveCell.Value
End If
End Sub

--
Cheers
Nigel



"Sandip" <sanshah01@yahoo.com> wrote in message
news:1123652257.178981.140550@g44g2000cwa.googlegroups.com...
> Hi,
>
> I have a range of cells say from A1 to A20 having certain numbers. I
> would like the output of any of these cell range in C1 depending on
> what the active cell is. The active cell will always be one cell and
> not a range of cells.
>
> For eg, If the active cell is A5, the value of A5 should appear in C1.
> As the person presses the down arrow and scroll downs the list, the
> number in C1 should change accordingly.
>
> The output in C1 is supposed to drive various Vlookup formulas.
>
> If the active cell is no longer within the A1 to A20 range, the last
> selected cell in A1 to A20 should remain in C1.
>
> Appreciate anyone's help..
>
> Thanks
> Sandip.
>

Hi Simon,

I have used your code and it does work however I have one problem. The
below macro is picking up the all the active cells and copying it in J5
even if its not within the range B8:B30.

What extra code do I need to add so that if any cell other than the
range is active, the same should not be pasted in J5.

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("b8:b30")
For Each cell In rng
Range("j5").Value = ActiveCell.Value
Next
End Sub

Regards
Sandip.

Go with Nigel's code it's better