Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

How to get rid of the Vlookup Path in a Formula

Hi Guys, I have a Lookup to another Workbook using Index Match. Is there a way I can shorten my formula using just the name of the spreadsheet instead of its whole path?


eg. instead of =IF(ISNA(INDEX('V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\12_2013 Recs\BS\[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\12_2013 Recs\BS\[Murex BS rec breaks - 031213.xlsm]R

to "=IF(ISNA(INDEX('[Murex BS rec breaks]Rec'!$B:AL,Match(True,Index('[Murex BS Rec Breaks]" thus getting rid of the whole path of the spreadsheet in the formula
0
Justincut
Asked:
Justincut
  • 3
  • 3
  • 2
  • +1
1 Solution
 
andrew_manCommented:
You can find somewhere to store the path

Then, use indirect(cell).  If you dun know how to do, I can teach you step by step.
0
 
NBVCCommented:
If you have Excel 2007 or later, you can use IFERROR.

The formula I gave you on the previous thread would be reduced to:

=IFERROR(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$B=$B556,0),0),AD$1),IF($J556="","NEW",IFERROR(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$C=$C556,0),0),AD$1-1),"NEW")))
0
 
andrew_manCommented:
My suggestion is keep the link at another cell rather than in formula.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NBVCCommented:
INDIRECT() won't work if the OP closes the workbook at any point.  Also, the OP is looking up a value in a range... you will need to use VLOOKUP, INDEX/MATCH or some UDF to achieve the lookup....
0
 
andrew_manCommented:
I think just update the cell and open the workbook.
0
 
Rob HensonIT & Database AssistantCommented:
As NB_VC says INDIRECT doesn't work when the source workbook is not open. However, there is another option, download the MULTIFUNC add-in and one option in that is INDIRECT.EXT which allows INDIRECT to work when source workbook is closed.

I can't remember the source of the Add-In but a Go Ogle search would probably find it.

Thanks
Rob H
0
 
NBVCCommented:
Rob, you are talking about Morefunc.xll addin I think.  That add-in is not reliable for versions after 2003 as far as I recall.
0
 
Rob HensonIT & Database AssistantCommented:
Indeed, I didn't think MULTIFUNC was right when I was typing it!!

Another option would be to create a Named Range that refers to a range in an external workbook, you can then just use the Range Name instead of the full file path.

Thanks
Rob H
0
 
JustincutAuthor Commented:
If I use a Named Range called "BS Rec", for example.How would this formula look like? =IF(ISNA(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$B=$B556,0),0),AD$1? Also, is there not a more simplistic Index Match formula?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now