I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and how
do I overcome this problem?

Cheers,
Soo Ting

INDIRECT doesn't work with closed workbooks.


You could try Harlan Grove's PULL function. You can get it
ftp://members.aol.com/hrlngrv/ (look for pull.zip)

You would use it like so

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!"&"B5")

or

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!B5")


Alternative

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Hi again.

I have something to add on. I've experimented a couple of scenarios for the
INDIRECT function, and here are the conclusions:

The function works if the following worksheet names are used:

1) Cat
2) C01
3) C_test

but does not work for the following:

1) C01_XXX

Could it be a combination of the Letter "C" and underscore?

Help me out here please!

Thank you very much.

"Agent Ting" wrote:

I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and how
do I overcome this problem?

Cheers,
Soo Ting

Hi Bob,

Thank you, but I was not referring to closed workbooks. It's merely within
the same workbook, just different sheets. Can't understand why though. See
my later post as well please.

Thank you very much.

Cheers,
Agent Ting


"Bob Phillips" wrote:

INDIRECT doesn't work with closed workbooks.


You could try Harlan Grove's PULL function. You can get it
ftp://members.aol.com/hrlngrv/ (look for pull.zip)

You would use it like so

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!"&"B5")

or

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!B5")


Alternative

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

It works but you need to pad the sheet name with '
so assume you have the text string C01_XXX in A1 and you want to return
what's in C2 in that sheet
then one way would be to use

=INDIRECT("'"&A1&"'!C2")


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Agent Ting" wrote in message
...
Hi again.

I have something to add on. I've experimented a couple of scenarios for
the
INDIRECT function, and here are the conclusions:

The function works if the following worksheet names are used:

1) Cat
2) C01
3) C_test

but does not work for the following:

1) C01_XXX

Could it be a combination of the Letter "C" and underscore?

Help me out here please!

Thank you very much.

"Agent Ting" wrote:

I have written an indirect function in a cell which looks up to a
worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT
does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Tried.

Still not working.

Anyone please?

Cheers,
Ting

"Peo Sjoblom" wrote:

It works but you need to pad the sheet name with '
so assume you have the text string C01_XXX in A1 and you want to return
what's in C2 in that sheet
then one way would be to use

=INDIRECT("'"&A1&"'!C2")


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Agent Ting" wrote in message
...
Hi again.

I have something to add on. I've experimented a couple of scenarios for
the
INDIRECT function, and here are the conclusions:

The function works if the following worksheet names are used:

1) Cat
2) C01
3) C_test

but does not work for the following:

1) C01_XXX

Could it be a combination of the Letter "C" and underscore?

Help me out here please!

Thank you very much.

"Agent Ting" wrote:

I have written an indirect function in a cell which looks up to a
worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT
does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Hi

Probably your string and sheet name don't match really. Maybe your sheet
name has some trailing spaces, or some space somewhere in it? Or you do have
'O' instead of '0' ?


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Hi

Seeing that how promptly I'm replying this message, it shows that this is
really important. Nope, I can assure you that I have checked the spelling
and all. As a matter of fact, I urge everyone to try this function on a
blank workbook and tell me your findings. It's something I really can't
explain but it just wouldn't work with worksheet with names starting with the
letter "C" and with a combination of numbers "01".

Anyone else please help?

Help appreciated!

Many many thanks.

"Arvi Laanemets" wrote:

Hi

Probably your string and sheet name don't match really. Maybe your sheet
name has some trailing spaces, or some space somewhere in it? Or you do have
'O' instead of '0' ?


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Sorry all. I apologise for the spamming. I left out the close inverted
commas.

Thanks Peo, it worked after all.

Cheers!

"Agent Ting" wrote:

Tried.

Still not working.

Anyone please?

Cheers,
Ting

"Peo Sjoblom" wrote:

It works but you need to pad the sheet name with '
so assume you have the text string C01_XXX in A1 and you want to return
what's in C2 in that sheet
then one way would be to use

=INDIRECT("'"&A1&"'!C2")


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Agent Ting" wrote in message
...
Hi again.

I have something to add on. I've experimented a couple of scenarios for
the
INDIRECT function, and here are the conclusions:

The function works if the following worksheet names are used:

1) Cat
2) C01
3) C_test

but does not work for the following:

1) C01_XXX

Could it be a combination of the Letter "C" and underscore?

Help me out here please!

Thank you very much.

"Agent Ting" wrote:

I have written an indirect function in a cell which looks up to a
worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT
does
not work with worksheets starting with the letter "C". Is this true and
how
do I overcome this problem?

Cheers,
Soo Ting

Yes, =INDIRECT('C01_XXX'!C2) works fine for me, as does
=INDIRECT("'"&A1&"'!"&C2)
--
David Biddulph

"Agent Ting" wrote in message
...
Hi

Seeing that how promptly I'm replying this message, it shows that this is
really important. Nope, I can assure you that I have checked the spelling
and all. As a matter of fact, I urge everyone to try this function on a
blank workbook and tell me your findings. It's something I really can't
explain but it just wouldn't work with worksheet with names starting with
the
letter "C" and with a combination of numbers "01".

Anyone else please help?

Help appreciated!

Many many thanks.

"Arvi Laanemets" wrote:

Hi

Probably your string and sheet name don't match really. Maybe your sheet
name has some trailing spaces, or some space somewhere in it? Or you do
have
'O' instead of '0' ?


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a
worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT
does
not work with worksheets starting with the letter "C". Is this true
and
how
do I overcome this problem?

Cheers,
Soo Ting

Hi David,

=INDIRECT('C01_XXX'!C2) doesn't work for me; however
=INDIRECT("'"&A1&"'!"&C2) produces the results from cell C2 from the
'C01_XXX'! worksheet.

"David Biddulph" wrote:

Yes, =INDIRECT('C01_XXX'!C2) works fine for me, as does
=INDIRECT("'"&A1&"'!"&C2)
--
David Biddulph

"Agent Ting" wrote in message
...
Hi

Seeing that how promptly I'm replying this message, it shows that this is
really important. Nope, I can assure you that I have checked the spelling
and all. As a matter of fact, I urge everyone to try this function on a
blank workbook and tell me your findings. It's something I really can't
explain but it just wouldn't work with worksheet with names starting with
the
letter "C" and with a combination of numbers "01".

Anyone else please help?

Help appreciated!

Many many thanks.

"Arvi Laanemets" wrote:

Hi

Probably your string and sheet name don't match really. Maybe your sheet
name has some trailing spaces, or some space somewhere in it? Or you do
have
'O' instead of '0' ?


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Agent Ting" wrote in message
...
I have written an indirect function in a cell which looks up to a
worksheet
naming "C01_XXX_YYY", but it didn't work. My colleague says INDIRECT
does
not work with worksheets starting with the letter "C". Is this true
and
how
do I overcome this problem?

Cheers,
Soo Ting