Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

The function you need is INDIRECT.
--
David Biddulph

"QuietMan" wrote in message
...
Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

I know the function, but I cannot get the syntax correct....any help there?
--
Helping Is always a good thing


"David Biddulph" wrote:

The function you need is INDIRECT.
--
David Biddulph

"QuietMan" wrote in message
...
Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

=INDIRECT("'C:\Documents and
Settings\FP&A\Templates\[SMP2Data.xls]"&your_cell_ref_which
contains_ref_to_Sheet2&"'!$A$1")
--
David Biddulph

"QuietMan" wrote in message
...
I know the function, but I cannot get the syntax correct....any help there?
--
Helping Is always a good thing


"David Biddulph" wrote:

The function you need is INDIRECT.
--
David Biddulph

"QuietMan" wrote in message
...
Does anyone mknow how I would reference "Sheet2" to a cell in a
worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson

Thanks, works like a charm...I'm now able to set up the same range on
multiple worksheet tabs in a different file and reference the different data
by changing the Tab names

Again Many thanks
--
Helping Is always a good thing


"David Biddulph" wrote:

=INDIRECT("'C:\Documents and
Settings\FP&A\Templates\[SMP2Data.xls]"&your_cell_ref_which
contains_ref_to_Sheet2&"'!$A$1")
--
David Biddulph

"QuietMan" wrote in message
...
I know the function, but I cannot get the syntax correct....any help there?
--
Helping Is always a good thing


"David Biddulph" wrote:

The function you need is INDIRECT.
--
David Biddulph

"QuietMan" wrote in message
...
Does anyone mknow how I would reference "Sheet2" to a cell in a
worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing


"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson

The =indirect.ext() has worked for lots of people. I'd bet you built the
formula incorrectly.

But I don't know anything you can do to improve the speed.

QuietMan wrote:

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson


--

Dave Peterson

Dave,

Here is the syntax I'm using for the indirect.ext fromula

=OFFSET(INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!$A$1",0),0,0,10,1)

Let me know if it's not right

Thanks
--
Helping Is always a good thing


"Dave Peterson" wrote:

The =indirect.ext() has worked for lots of people. I'd bet you built the
formula incorrectly.

But I don't know anything you can do to improve the speed.

QuietMan wrote:

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson


--

Dave Peterson

IIRC, =offset() is another function that won't work with closed workbooks.

Maybe you could replace it with:

=INDIRECT.EXT("'P:\Financial Planning & Analysis\SmartPage2\[SMP2Data.xls]"
&Test!C$1&"'!A1:a10")

Or some sort of =index()???



QuietMan wrote:

Dave,

Here is the syntax I'm using for the indirect.ext fromula

=OFFSET(INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!$A$1",0),0,0,10,1)

Let me know if it's not right

Thanks
--
Helping Is always a good thing

"Dave Peterson" wrote:

The =indirect.ext() has worked for lots of people. I'd bet you built the
formula incorrectly.

But I don't know anything you can do to improve the speed.

QuietMan wrote:

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

It works now...Here is what I came up with since the offset will not work
with a closed workbook. Linktest2 sets the lookupRange and LinkTest3 sets
the sumRange. I'll look into adding an index() function to see if that would
also work

Thanks again

LinkTest2 =INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!A1:a10")
LinkTest3 =INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!"&Test!C$2&"1:"&Test!C$2&"10")
Lookup Formula =SUMPRODUCT((LinkTest=$C6)*(LinkTest3))

--
Helping Is always a good thing


"Dave Peterson" wrote:

IIRC, =offset() is another function that won't work with closed workbooks.

Maybe you could replace it with:

=INDIRECT.EXT("'P:\Financial Planning & Analysis\SmartPage2\[SMP2Data.xls]"
&Test!C$1&"'!A1:a10")

Or some sort of =index()???



QuietMan wrote:

Dave,

Here is the syntax I'm using for the indirect.ext fromula

=OFFSET(INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!$A$1",0),0,0,10,1)

Let me know if it's not right

Thanks
--
Helping Is always a good thing

"Dave Peterson" wrote:

The =indirect.ext() has worked for lots of people. I'd bet you built the
formula incorrectly.

But I don't know anything you can do to improve the speed.

QuietMan wrote:

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson