You need to use the Worksheet_Change event not selection_change

put this sub (after you have changed it to do what you want) in each
worksheet that you want to get the value from.
(double click the worksheet in the VBE project explorer)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
Worksheets("Sheet3").Range("b2") = Target.Value
End If
End Sub

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Minitman" wrote in message
...
Hey Charles,

I do not know to much about VBA Worksheet_Change events.
This workbook that I am working on has one at the sheet level and I
cannot get it to work on the workbook level.

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 6 And Target.Row = 3 Then
Range("A1").Value = Target.Offset(0, -4).Value
End If
End Sub

I tried changing the Range("A1") to Range("Customers!O1") and get a
Microsoft Visual Basic message box with this inside:

Run-time error'1004':
Method 'Range' of object '_Worksheet' failed

Any suggestions?

TIA

-Minitman

On Tue, 24 Feb 2004 20:24:23 -0000, "Charles Williams"
wrote:

Probably best to do this with VBA using worksheet_change events.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Minitman" wrote in message
.. .
Hey Charles,

You mentioned a formula in Z1, that is still missing. What I need is
a formula that can monitor cell "A1" on 20 sheets and whichever of
those 20 cells changes, to automatically copy the contents of that
changed cell into say cell "O1" on the Data sheet.

Is this possible
On Tue, 24 Feb 2004 10:43:53 -0000, "Charles Williams"
wrote:

I like to use CHOOSE wherever possible:

assume you have a formula in Z1 that gives 1 to 19 to determine which
named
range you want to use and that your named ranges are named One, two
etc
then
something like:

=CHOOSE(Z1,One,Two,three, ...)

CHOOSE is generally more efficient than INDIRECT because it is not a
volatile function.
Its disadvantages are that your formula has to resolve to 1 to n, and
that
you cannot use too many named ranges before the formula gets too long
or
you
hit the max 30 argument limit.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Bob Phillips" wrote in message
...
Hi Minitman,

Frank's example show you how to reference cell A1 in another sheet
using
indirect referencing.

You asked about names, and let's assume that you stored the text of
the
name
in A1, say myRange, and myRange points at Sheet3!A17, then you can
access
it, also musing INDIRECT, like so,

=INDIRECT(A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
you may use the function INDIRECT. e.g. if you store the sheetname
in
cell A1 you can use it as follows:
=INDIRECT("'" & A1 & "'!A1)
(note the multiple apostophes at the beginning and in the middle:
" '
"
and " ' !)

--
Regards
Frank Kabel
Frankfurt, Germany

Minitman wrote:
Greetings,

I have a formula that is looking at a named range (Sheet1!A1)
from
Sheet2. I need to add 19 more sheets (Sheet3 thu Sheet21). Is
there
anyway to change which named range that formula is looking at?

TIA

-Minitman