Referencing a worksheet name to a cell...

[QuietMan]

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

 

[David Biddulph]

The function you need is INDIRECT.

 

[QuietMan]

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

The function you need is INDIRECT.
--
David Biddulph

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

 

[David Biddulph]

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

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

The function you need is INDIRECT.
--
David Biddulph

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

 

[Dave Peterson]

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.

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

 

[QuietMan]

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

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

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

The function you need is INDIRECT.
--
David Biddulph

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

 

[QuietMan]

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

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.

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

 

[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.

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

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.

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

 

[QuietMan]

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

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.

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

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

 

[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()???

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

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.

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

:

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

 

[QuietMan]

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

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()???

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

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

:

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