Can A Named Range Be A Variable?
 

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

Can A Named Range Be A Variable?
 

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

Can A Named Range Be A Variable?
 

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

Can A Named Range Be A Variable?
 

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

Can A Named Range Be A Variable?
 

Thanks for the quick response!

I think that i need to clarify what I am trying to do here.

A vbe code on each year sheet (each sheet is labeled as a year: 1994,
1995,,,2004) picks up the contents of the cell in the chosen row when
the user clicks on a cell from a certain column in that row (eg. user
chooses G13 and the vbe code automatically places the contents of B13
into A1, G58 - B58 into A1, etc.). This works. However, in the Data
sheet, a formula to filter a customer list (showing only those
customers that fall within the start - end range) was written for only
one year sheet. As such, it is looking to A1 on THAT sheet.

The question is how can I get that formula to automatically know which
year sheet the user has triggered the vbe code changing the contents
of that sheets A1?

Any ideas?

TIA

-Minitman


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

Can A Formula Monitor Cells On Different Sheets And Copy Any Change? (Was:Can A Named Range Be A Variable?)
 

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

Can A Formula Monitor Cells On Different Sheets And Copy Any Change?
 

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

Can A Formula Monitor Cells On Different Sheets And Copy Any Change?
 

Hey Charles,

That works great - Thanks.

Only one more procedure left to complete this project. But that can
wait for the next post.

Again, thank you very much for your help.

-Minitman




On Wed, 25 Feb 2004 13:25:43 -0000, "Charles Williams"
wrote:

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