Vlookup closed files

[JackieRose]

Hi guys

I new to the forum so just wanted to say hi first

I have a folder with a number of excel files (invoices- saved as numberic invoice numbers) and what I want to do is instead of having to re type all the names, addresses and invoice totals out manually I want to create a spreadsheet that will look them up based on their invoice number - I have had go at doing it using a vlookup funtion but it does not seem to work

what i have is

=VLOOKUP(E2,'C;\Users\Public\Account_Invoices\Peggy\[E2.xlsx]Sheet1'!$A$1:$G$100,0)

any help would be great or even if anyone has some similar spreadsheets I can work off an example any help would be great as I have over a 600 invoices to type out if i can get it to work

cheers

JackieRose

[NBVC]

There are a couple of issues.

Firstly, in order to reference a cell to get the name of a file/sheet to look in, you need to use the INDIRECT() function.

Secondly, and more importantly, INDIRECT however, does not work on closed workbooks.

You can, however, download and install a free addin from here: MOREFUNC and use the INDIRECT.EXT function and this would work with closed workbooks.

=VLOOKUP(E2,INDIRECT.EXT("'C;\Users\Public\Account_Invoices\Peggy\["&E2&".xlsx]Sheet1'!$A$1:$G$100"),0)

[JackieRose]

Hi Thanks so much for the reply, I have downloaded the add on as suggested and have had a go at it but can seem to get it to work i just keep getting "invalid cell ref" error - I have attached my spreadsheet, could have a look and let me know where I'm going wrong?

I would really appricate it

Thanks again

JackieRose

[NBVC]

A few issues off hand...

1. After the path drive letter, there should be a colon ( : ) not semi colon ( ; )

2. $K$16 should be a table range like $A$1:$K$1000 where column A contains the array of values where the C2 value should be found within and column K is the last column in the table.

3. You are missing a column_index_number... (3rd argument in the VLOOKUP) that should represent the column number within the table array that you want to pull results from.... so if the table is $A$1:$K$1000 and you want to pull from column A based on finding a match in column A, then the column_index_number would be 11. Leave the 0 as the fourth argument to search for exact matches.

4. You path seems to contain the C2 value as part of it's name and you are also looking up the C2 value in that path... is that correct?


so e.g. of formula should be:

=VLOOKUP(C2,INDIRECT.EXT("'C:\Users\Public\Account_Invoices\Peggy\["&C2&".xlsx]Sheet1'!$A$1:$K$1000"),11,0)

[JackieRose]

Thanks so much again for the reply- don't think i've explained this to well to you and Im not sure if Im on the right track

I have a folder full of excel files (invoices) all saved as there invoice number (see attached file which is an example of one of the invoices

instead of having to type in all the details from all the individual invoices into my spreadsheet (so i can get a monthly totals etc) what i want to do is look up the invoices and copy the details from them to use in my spreadsheet

for example i want to look up invoice number 7200 (C2 of spreadsheet) and get the date of the invoice which is at cell K16 in the invoice and copy that date into cell B2 of my spreadsheet

I also want it to look up

- the invoice Net amount at cell L45 and copy to E2 of my spreadsheet
- the invoice Vat amount at cell L46 and copy to F2 of my spreadsheet
- the invoice Total at cell L47 and copy to G2 of my spreadsheet

So sorry for not explaining myself properly i really do appriecate all your help
JackieRose

[Cutter]

You don't need the VLOOKUP() function if you are directly referencing a specific cell.

Try this in B2:

=INDIRECT.EXT("'C:\Users\Public\Account_Invoices\Peggy\["&C2&".xlsx]Sheet1'!$K$16")

replace the highlighted Sheet1 with the exact name of the target file's sheet (if it isn't Sheet1).

Similary replace the $K$16 reference for the other cells you want to retrieve data from.
In cell E2 use $L$45
In cell F2 use $L$46
In cell G2 use $L$47

Here is a link to explain use of INDIRECT.EXT() function:

http://xcell05.free.fr/morefunc/engl...direct.ext.htm

[JackieRose]

Thank you both sooooo much for your advice it work great and now I also understand Vlookup also so cheers again you have helped save me a lotttt of time

Thanks again guys

JackieRose

[Cutter]

You're welcome.

Don't forget to mark your thread as SOLVED (click on the FAQ link at top of page for directions).