Making Cell equal contents of Selected Cell

[Sauron]

Hi There,

Hope someone can help!

What I am wanting to do is make a cell (any cell) equal the informatio
in a selected cell in a particular range for easy data entry (so the
can just select it), would there be a function that returns thi
particular information?

I want to concatenate some information and it would be so much easie
if the user could simply select a cell from a 'hot area' rather tha
having to remember that information and type it in somewhere else...

Am I being clear enough?

(Iam running Excel 2000 by the way)

Cheers!
Sa

 

[Bernie Deitrick]

Sau,

What you're describing is a feature called Data Validation. You can
set valid selections equal to a member of a list, or have the list be
linked to other cells (your 'Hot area'). Look in help for more on how
to use it.

HTH,
Bernie
MS Excel MVP

 

[Sauron]

Thanks very much for the reply.

I have had a good look through help and Data Validation but I still
cannot make it do what I want...

Cells;

A B C D
1 1 2 3 4

2 5 6 7 8

3 -1-

So simply by clicking on any of the cells in range A1-D2 makes cell A3
= the number selected in that range. Like the above where cell A1 is
selected so A3 changes to mirror that selection or if B2 is selected A3
would equal 6 and so on.

 

[Dave Peterson]

One way:

rightclick on the worksheet tab that should have this behavior. Select View
Code and paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1:d2")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Range("a3").Value = Target.Value
Application.EnableEvents = True

End Sub

Back to excel and try it out.

You could add a little checking to see if A3 already had something in it--if you
wanted to prevent overwriting that value.

And this doesn't actually work by clicking on the cell. It works by selecting
the cell--arrow key or mouse.

 

[Sauron]

Thats it!!

Wow thanks SO much!!! Tremendous stuff