Indirect Vlookup into external file, specifying a tab contained in a cell in the worksheet

Hi there hopefully someone can help me out


I need to create an vlookup which will look up a value from a cell which is in a different file on a specific tab.

Here is the formula I currently am using to vlookup the account in cell g7 from the tab name in E78

=IF(ISBLANK(D7),"",IFERROR(VLOOKUP($G7,INDIRECT($E7&"!$P$1:$S$65536"),4,FALSE),"Check Before Report"))

This requires reports to all be in the same file where I use this formula, I would need to be able to store them in a different file. I understand I'll need to have the file open, that is not a problem. Also I need to be able to have the cell for the tab (E78) to be able to be a concatenated cell.

This also needs to all be on one line in the worksheet.


Any help would be appreciated

Thanks!

Bit confusing, as you refer twice to the tab name being in E78 but use E7 in the formula. Also, you don't really say what your problem is - I presume the formula isn't working for you.

In addition to the sheet name, you also need to include the filename (even if the file is open), and this needs to look like [filename.xlsx], which you could put into another cell or put explicitly in the formula. Also, it is a good idea to include apostrophes around the filename and sheet name, in case either of them include spaces. So, assuming you put the filename (only, without the extension or square brackets) in X1, then your formula would become:

Formula:

Please Login or Register  to view this content.

Personally, I don't like ISBLANK - what's wrong with IF(D7="","", ...

Hope this helps.

Pete

My mistake, E78 is just E7. I used your formula and saved my reports file to X1, in this format. C:\desktop\Before Reports

Here is the formula, its now on row 11


=IF(D11="","",(VLOOKUP($G11,INDIRECT("'["&$X$1&".xlsx]"&$E11&"'!$P$1:$S$65536"),4,FALSE)))

Also got rid of the iferror, but now I'm getting a #ref! error

do you see anything wrong with that? t

You don't need the full path in X1 - just the filename, Before Reports.

Also, check that the file is saved with an .xlsx extension rather than .xls (change this just before the closed square bracket if necessary).

Hope this helps.

Pete

You're awesome! That worked!

Thanks

Well, that's good to hear - thanks for feeding back. Just one other thing - you don't need to include the row references if you want full columns, so instead of $P$1:$S$65536 you can write this as P:S. You don't even need the $ symbols as they are included within quotes and thus will not change if you copy the formula across.

Perhaps you can mark the thread as Solved if you consider it to be so (the FAQ describes how), and you might like to click on one of the "star" icons in the bottom left corner of any post that has helped you, in order to pass on your thanks more directly.

Pete

EDIT: You can then put your IFERROR back in, once the formula is working.