Solved

How to get rid of the Vlookup Path in a Formula

Posted on 2013-12-05
9
420 Views
Last Modified: 2013-12-11
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
Comment
Question by:Justincut
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698232
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
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39698376
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698393
My suggestion is keep the link at another cell rather than in formula.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39698403
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39698419
I think just update the cell and open the workbook.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39698517
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39698525
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39698538
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
 

Author Comment

by:Justincut
ID: 39703150
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now