Creating a complete filename for use with functions.

  • Thread starter Thread starter cothrang
  • Start date Start date
C

cothrang

I am attempting to set a spreadsheet that uses a cell value to identif
a filename. Example.

Cell A1 = "doej"

Cell A2 = "C:\timesheet\"&A1&".xls"

Cell B1:B3 has 1, 2, 3

My function is simple, to get it to work....

=sum(a2!$b$1:$b$3)

I know that's not right, but I think it explains what I'm trying to do
It's probably very simple, but I've not had any luck. I've also mad
Cell A2 = the entire string, including the cells I want to su
("'C:\timesheet\"&A1&".xls[sheet1]'!$b$1:$b$3"). Still doesn't work.

Any suggestions?

Thanks for your time and attention!

Gre
 
Hi
two problems:
First you may use INDIRECT for this:
=SUM(INDIRECT("'[" & A1 & ".xls]sheet2'!$b$1:$b$3")
this will work only if the other workbook is opened (also n ote the
multiple apostrophes at the beginning)

If the other workbook is closed try the following:
have a look at the Add-In MOREFUNC.XLL
(http://longre.free.fr/english)
use the function INDIRECT.EXT.e.g.
=INDIRECT.EXT("''C:\Documents and Settings\" & cell_ref1 &
"\MyDocuments\My folder\[" & cell_ref2 & cell_ref3 & ".xls]Expense
Statement'!$F$2")
thats is just replace your function INDIRECT with INDIRECT.EXT

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u
 
Back
Top