INDIRECT

A

Agent Ting

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
 
B

Bob Phillips

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)
 
A

Agent 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.
 
A

Agent 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 said:
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 said:
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
Click to expand...
Click to expand...
 
P

Peo Sjoblom

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)
 
A

Arvi Laanemets

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' ?
 
A

Agent 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.
 
A

Agent Ting

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

Thanks Peo, it worked after all.

Cheers!
 
D

David Biddulph

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

pshepard

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