Vlookup several sheets in another workbook

[nisha1110]

Wow that really helped!

I have another query now, similar to the one above. This relates to our Purchase invoice board.

All of our invoices are internally numbered, the info is entered onto the attached spreadsheet. A register, source of all Purchase information. (this sheet was not created by me by the way, its really old and my manager does not want to change it )

I would like to create a spreadsheet of the invoices that i have placed under query, i have set out a simple template at the moment which i use. But i have to input all the info from the invoice on this sheet, I can't help but wonder if the vlookup functon would work on for this.

I would like to enter our internal invoice no into my query spreadsheet and with the vlookup function i would like to retrieve the info from our purchase invoice spreadsheet

Only thing is, our invoice num are continously rolling throughout the year. New numbers are not created for the month, it continues from the last invoice number. However our invoices are filed on a monthly basis (hence the month tabs below).

Is there a way that a lookup function can be retrieve info from several worksheets at the same time in a different workbook?

[NBVC]

So all you want to do is enter an invoice number and the corresponding information will be returned into your query sheet?

Does the invoice number you type only appear once in all the spreadsheets?

[nisha1110]

Yes the number would only appear once in the workbook. Unique reference for that invoice.

[NBVC]

The easiest way is to first download and install a free addin called Morefunc.xll from here:

Morefunc

and in your new workbook apply this formula:

=VLOOKUP($A1,THREED('[Purchase Invoice Board June 08 - May 09.xls]March 09:Jan 09'!$A$4:$J$400),COLUMNS($A$1:B$1),0)

Where A1 is the cell your invoice to query is in....

and '[Purchase Invoice Board June 08 - May 09.xls]March 09:Jan 09'!$A$4:$J$400 covers the range in all sheets in the queried workbook.

This pulls from column B of the queried workbooks.

You can then copy formula across the columns to get all other relevant data.

[nisha1110]

Hi

I'm sorry i should have mentioned that i have no Excel training whatsoever. I have installed the morefunc and integrated it to our Excel program.

However now I am stuck, as i do not know how to get your formula to work, i simply have no idea where to paste it to work. I tried playing around and i kept getting #REF! in the cells.

I really should have attached my query spreadsheet too. (it is now)

The cells in yellow is where i think the vlookup formula should go, cell H9 is where I will enter our unique invoice ref. For now it is just these 2 cells that the vlookup needs to be in.

Thanks

[franciz]

the morefunc is an addin, have you add it to your addin? Go to Tools >> Addins
and if you don't find it in the dialog box, click browse to where you have saved it
click Ok. I can't tell for sure as you didn't provide the source sheets which hold
your data.

my guess is change the lookup cell's reference in the formula

HTH

[NBVC]

Put this formula in I9.

=VLOOKUP($H9,THREED('[Purchase Invoice Board June 08 - May 09.xls]Mar 09:Jan 09'!$A$4:$J$400),3,0)

It should pull up the CompanyInvoice number

I changed the Columns() part to hard coded 3 because it seems you are pulling from different columns and not necessarily consecutive columns...

so you would have to paste the same formula in the other cells and change just the 3 to the appropriate column number you want to extract from.

Make sure that the reference file has still the same name and that the sheet names are the same... i.e. you may have to change this part around '[Purchase Invoice Board June 08 - May 09.xls]Mar 09:Jan 09' to suit your actual workbook identity.

This part: $A$4:$J$400 references the whole table on each sheet so you may also need to adjust to ensure all data is being looked at.

[NBVC]

Did you install the Morefunc.xll addin that was suggested in Post # 4?

[nisha1110]

Hi

Yes I did, i clicked the link, downloaded Morefunc, it then asked me to save the file, which i did to My Documents, the file was a setup.exe, i clicked it and it asked if i wanted to integrate it to my current Excel, which I did and it installed successfully...i think.

I even had a look in the addins tool thing and it was there, please see screenshot attached of it.

[NBVC]

I just created a workbook named: Purchase Invoice Board June 08 - May 09.xls

with 3 tabs named Mar 09, Feb 09 and Jan 09

and the formula I posted worked fine.

If you move the tabs around in the file, you may get REF# error... or if there are extra spaces in the sheet names, or file name, etc... you will get error...

Have a look at those... and look again at the formula to ensure you have the Mar 09:Jan 09 part right.

[nisha1110]

Hi,

My apologies for the really late reply again, i have been working on this for a while, changing everything possible so the formula would work.

Eventually i worked out why it was not working and the reason behind the #REF!.

I had to remove ALL spaces/gaps from the tab names, I then had to remove any spaces/gaps and - in the file name and redo the formula.

It now looks like this:

=VLOOKUP($H9,THREED([PurchaseInvoiceBoardJune2008toMay2009.xls]May09:Jun08!$A$4:$J$400),3,0)

It does look messy, but the formula works! I don't know why it didn't work for me the way you set it out, probably something to do with our software and yours.

Thank you for the help. For some reason it won't allow me to edit my first post to mark it solved