Making Cell equal contents of Selected Cell

  • Thread starter Thread starter Sauron
  • Start date Start date
S

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
 
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
 
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.
 
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.
 
Back
Top