• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to understand the logic of the formula

C

chintan_1985

New Member
IF(ISERROR(+VLOOKUP($B2193,INDIRECT("'"& TEXT(E$10,"dd-mmm-yy")& "'"&"!$B$7:$Z$2500"),$A$3,0))," ",VLOOKUP($B2193,INDIRECT("'"& TEXT(E$10,"dd-mmm-yy")& "'"&"!$B$7:$Z$2500"),$A$3,0))
 
Hi chintan_1985,


IF(ISERROR(+VLOOKUP($B2193,INDIRECT("'"& TEXT(E$10,"dd-mmm-yy")& "'"&"!$B$7:$Z$2500"),$A$3,0))," ",VLOOKUP($B2193,INDIRECT("'"& TEXT(E$10,"dd-mmm-yy")& "'"&"!$B$7:$Z$2500"),$A$3,0))


The formula can safely be simplied like:


IF(ISERROR(Formula)," ",Formula))


Where "Formula" equals:


+VLOOKUP($B2193,INDIRECT("'"& TEXT(E$10,"dd-mmm-yy")& "'"&"!$B$7:$Z$2500"),$A$3,0)


..moving inside out, the TEXT() converts the contesnts of E$10 to "dd-mm-yy' format, that is used as Sheet Name In INDIRECT() function, and $B$7:$Z$2500 is used as Range Refrence. Thus it becomes [SheetName]!$B$7:$Z$2500.


The VLOOKUP() function lookup up for $B2193 in [SheetName]!$B$7:$Z$2500 and returns a value with exact math (the Last zero in vlookup) from the column number specified in $A$3.


If the result is an error, IFERROR will give you a Blank, otherwise you will get formula executed.


Hopefully that helps.
 
Thanks for the reply.


Just want to know that can we use external file name instaed of sheet name?


And if yes,


then what is the writing convention indicate the Excel that this is from linked to sheet of this one is from external file> sheet Name.
 
Hi Chintan_1985,


You can use filename this way, but that file need to be opened if you want to do some calcualtion, otherwise will need some sought of pull-type macro. That you can find though Google.


Regards,
 
Ok thanks Faseeh!!!


Just wat to confirm that the writing convention for the sheet name( in the same file) and sheet name ( in other workbook) will remain the same?
 
Hi chintan_1985,


I suggest you to open two workbooks and make a cell reference from one to another, this will be the best way to understand it.


Faseeh
 
Hi Chintan ,


You need to add the filename as follows :


The operative part of your formula within the same workbook is :


VLOOKUP($B2193,INDIRECT("'"&TEXT(E$10,"dd-mmm-yy")&"'"&"!$B$7:$Z$2500"),$A$3,0))


If you now need to link it to an external workbook , you will need to add the filename :


VLOOKUP($B2193,INDIRECT("'"&"[filename.xlsx]"&TEXT(E$10,"dd-mmm-yy")&"'"&"!$B$7:$Z$2500"),$A$3,0)


The filename is enclosed in square brackets , and the whole string is enclosed in double quotes.


Narayan
 
A word of caution: INDIRECT works with open workbooks only. So you will get error when you will close reference workbook.
 
You must log in or register to reply here.
Back
Top