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

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

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

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

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

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