I want to combine three cells with 'Concatenate' function and get the result
like

cell A1 = 10 (emp code)
cell B1 = filename.xls
cell C1 = sheet1'!A1

my coding is

=concatenate(a1,b1,c1) --- it should show the content of cell A1 in
10filename.xls , since the A1 is changeable.

rpmani

Try the below. (Hope 10filename.xls is open)

=INDIRECT("'["&A1&B1&"]" & C1)

--
Jacob (MVP - Excel)


"rpmani" wrote:

I want to combine three cells with 'Concatenate' function and get the result
like

cell A1 = 10 (emp code)
cell B1 = filename.xls
cell C1 = sheet1'!A1

my coding is

=concatenate(a1,b1,c1) --- it should show the content of cell A1 in
10filename.xls , since the A1 is changeable.

rpmani

The function you'd want to use is =indirect().

But =indirect() won't work if the sending file is closed.

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

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

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/30...-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

rpmani wrote:

I want to combine three cells with 'Concatenate' function and get the result
like

cell A1 = 10 (emp code)
cell B1 = filename.xls
cell C1 = sheet1'!A1

my coding is

=concatenate(a1,b1,c1) --- it should show the content of cell A1 in
10filename.xls , since the A1 is changeable.

rpmani


--

Dave Peterson