Microsoft Excel - vlooup and indirect - Asked By sudhakar srivatsav on 26-Nov-11 03:44 AM

I have been using vlookup and indirect function, but when use the same function different file (closed) result is


what is the problem in my formula as given below.
  B                    D                              E          

1 C:\Documents and Settings\10243\Desktop\Pradeep\Basis\[lokesh.XLS]lokesh
2 TCode Transaction Text lokesh
3 AL08 Reverse Line Items  VLOOKUP(B3,INDIRECT("'"&D1&"'!$A$1:$C$6553"),3,FALSE) 

Jackpot . replied to sudhakar srivatsav on 27-Nov-11 01:08 AM
Hi Sudhakar

INDIRECT() do not work with closed files.

Try the function available with the free Add-in INDIRECT.EXT
Anil Kumar replied to sudhakar srivatsav on 27-Nov-11 05:33 AM
Hi Sudhakar,

The Excel INDIRECT function has two arguments:

  1. ref_text: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range.
  2. a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.
 If the Excel INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

An INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed. In this example, you'll create a formula with the Excel INDIRECT function, using references to a file name, sheet name and cell name.

  1. In an open file named TestFile.xls, on a worksheet named Test Data, enter numbers in cells A1:A10
  2. On a sheet in a different workbook, in cell A2, enter the file name: 
       Test File.xls
  3. In cell A3, enter the sheet name: Test Data
  4. In cell A4, type a cell name from the range of numbers, e.g.: A7
  5. To see the syntax that you'll need to use in your formula, select cell A7, and type an equal sign:   =
  6. Switch to Test File.xls, and click on cell A7
  7. In the formula bar, you'll see the reference that was created:
         ='[Test File.xls]Test Data'!$A$7
  8. You'll create an INDIRECT formula that uses the same syntax, adding the single quote marks, square brackets and apostrophe.
  9. In cell A6, type the following formula:
    =INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)
  10. The first part of the string is a single quote and square bracket, within a set of double quotes:
       " ' [ "  (spaces were added for clarity)
  11. After the reference to A2, which contains the file name, is a square bracket, within a set of double quotes:
       " ] "  (spaces were added for clarity)
  12. After the reference to A3, which contains the sheet name, is a single quote and apostrophe, within a set of double quotes:
       " ' ! "  (spaces were added for clarity)
  13. The string ends with a reference to cell A4, which contains the cell address.
  14. Single quotes are included in the string to prevent errors if the sheet name contains space characters. 
  15. Press the Enter key, and the formula returns the number in cell A7 on the Test Data worksheet, in the Test File.xls workbook.
  16. Change the values in cells A2:A5, and the result in A6 will change.
  17. You can delete the sample link in cell A5 

Note1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function: =IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))
Also, you can use INDIRECT.EXT, this function works as INDIRECT but in addition to that it also works for closed workbooks. Hope this helps you. Do update us again, Thank you Anil

Pichart Y. replied to sudhakar srivatsav on 27-Nov-11 11:08 PM
Hi Sudhakar srivatsav,

This is the limitation of Indirect( ) function, both files must be open, otherwise the result will be #REF!

For work arround, if indirect( ) is what you really need to use it,  just for facilitating, easy working...I suggest you to save all your linked files (by indirect( ) function), as work space...with this way whenever you open the workspace file, all files that you save into same work space will be opened automatically...

To save files as workspace, go to menu View then at submenu "Save Work Space".

Hope this help.

Pichart Y.