Importing a range of data from a closed workbook

Hi,

Due to some cutbacks the company I work for has taken away the database system we used to use to record debts owed to us, so I'm building one in excel (no access or sql otherwise I'd use those). We have all the debt in seperate workbooks by financial period and I'm creating a new workbook that will upon entering of a specific financial period will pull information from the pertaining workbook and report on the information.

I have a Formula

=INDIRECT.EXT(CONCATENATE("'C:\Documents and Settings\hughel13\My Documents\[",Reports!$C$11,".xls]Sheet1'!R2:R138"))

Among others which pull other ranges

Which as I understand should work just fine on a closed workbook, however it only works when the target workbook is open.
When the workbook is closed the formula only pulls the first value in the range.

On a related note the above formula and it's counterparts are autofilled down the page from 2 to 1000 as some of the workbooks have 1000 records to pull, however the ones that dont have that many, cause errors when the data is pulled, is there a way to make it so that if the cell it's pulling from is empty it doesn't pull from it or pulls a value of 0?

Any ideas?

It should work, yes...

as a test, does this alternate work?


=INDIRECT.EXT("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"))

Nope that doesn't work properly either, only pulls the first value in the range.

Incidently this works perfectly with the workbook open or closed

='C:\Documents and Settings\hughel13\My Documents\[2009-2010.xls]Sheet1'!R2:R138

However

="'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"

=Concatenate("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138")

=Indirect(Concatenate("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"))

=Indirect.ext(Concatenate("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"))

=Indirect.ext("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"))

All do not work.

I can't exactly see why it does not work... the formula looks ok....

Do you have the latest version of Morefunc.xll installed?

Does this one work with workbook open?

=Indirect(Concatenate("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!R2:R138"))

What errors are you getting?

Are you sure cell Reports!$C$11 contains exactly matching text as the sheet name you want to link to.. check for spaces, etc...

What exactly is that formula supposed to do anyways?

all it is doing is referencing a range... but there is not manipulation or calculations.. and unless you enter it as an array formula, it should only extract the first value.. in the range... ie...Sheet1'!R2

Originally Posted by sigfreid

I'll attatch copies of the workbooks if that would help, will take a little while tho as I'll have to create fake data as it's somewhat sensitive information.

I think that's the best thing to do...

Can't seem to upload the copies I get a "databse error" from the website O.o

Edit: they were too large about 4mb total, so I did zip them to about 1.2mb and still got an error, trying again tho.

Edit2: Still getting an error >.<, possibly because I'm at work, I'm home in around 2 hours I'll try again then.

Maybe too large... try zipping them...

I got it to work, obviously having to change path to my directories...

Try:

=INDIRECT.EXT("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!$R"&ROW($A2))

Copied Down

You can add the FALSE parameter allowed for making the function non-Volatile:



=INDIRECT.EXT("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!$R"&ROW($A2),FALSE)

per Indirect.Ext help: hit Ctrl-Alt-F9 to update the results

Please start another thread and link to this one if necessary for reference.

The formula is using the value in Reports sheet, cell C11 to get the workbook it needs to search, and in that workbook it will go to sheet1, column R and row determined by ROW($A2) which results to 2, so Sheet1!R2 to get the info it needs.