We help IT Professionals succeed at work.
Get Started

Using the INDIRECT function in a VLOOKUP to an external workbook

John Carney
John Carney asked
Last Modified: 2012-08-14
I have a workbook with named ranges for 52 different workbooks, one for each week of the year.
Tails21 (for Week 21's book) is this:
='\\taus-inas3\toledo_share\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\ACA\3_Working Files\2BCoded\[ACA Week 21_ToBeCoded.xls]TailAvailability'!$C$6:$R$204
Tails22 (for Week 22's book) is this:
='\\taus-inas3\toledo_share\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\ACA\3_Working Files\2BCoded\[ACA Week 22_ToBeCoded.xls]TailAvailability'!$C$6:$R$204
etc., etc.

I have these formulas in a table:
C6 =VLOOKUP($B5,Tails21,4,0)
D6 =VLOOKUP($B5,Tails22,4,0)
etc., etc.

In C4 I have the full path and name  of Week 21's workbook
In C5, I have "Tails21"
In D4 I have the full path and name  of Week 22's workbook
In D5, I have "Tails22"
etc., etc.

Can I use the INDIRECT function referencing the values in Row 5 to recreate my vlookup formulas?
If so, what would the full formula be?

ORif I defined the named range "Tails" in each of my external workbooks, could I then reference the Row 4 values to look up that defined range in each workbook. If I did that, then what would my VLOOKUP formulas look like??

I've tried a bunch of different possibilities and haven't hit upon anything yet that works.

Watch Question
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE