Insert filepath into VLOOKUP function?

Question

Howdy all experts,

I have a report file with a simple VLOOKUP function to an external file that contains a month as part of the file path:

=VLOOKUP($BR3,'G:\Reports\JANUARY[Monthly Report.xlsx]Summary'!$B1:$F$50,5,FALSE)

The report summarizes 4 months.  I would like to be able to insert the next month into the existing file path within the VLOOKUP function for each month in an effort to automate the report.

I have the complete formua for each month as text (using concatenation).  Is there any way to convert an existing text formula into a real formula:

'=VLOOKUP($BR3,'G:\Reports\FEBRUARY[Monthly Report.xlsx]Summary'!$B1:$F$50,5,FALSE)

I've tried find and replace on the '=' sign, using named range constants within the file path and the INDIRECT function to no avail.

Any suggestions?

Thanking you in advance,

Mike

Answer 1

Hello Mike,

What I would do for this exercise so that Indirect can be used is to add a worksheet for a linked copy of the Table Arrays for the formula. For this we will name the worksheet Table Arrays.

Copy the rangeG:\Reports\JANUARY[Monthly Report.xlsx]Summary'!B1:F50 and Paste Special a link into the Table Arrays sheet starting at cell A1. (Think about linking more than 50 rows if the table in the source is likely to grow but not necessarily the entire columns because with links in the entire column might make the workbook too large.)

Name the linked range. I would not use actual month names like "January" or "Jan" because I don’t know if that could cause conflicts. I would use "MthJan"

Repeat for the remaining workbooks but place them across in adjacent columns perhaps with a column in between to make for easier reading. Name the additional tables MthFeb, MthMar etc.

Now the links should update with any change in the source workbooks.

Then your formula would be as follows with the array name for the required month.

=VLOOKUP($BR3,MthMar,5,FALSE)

If the table array required in your formula is to be dynamic based on the current month etc then you could also insert another table on your Table Array sheet like the following and name it "MyTblArry".

1	MthJan
2	MthFeb
3	MthMar
4	MthApr
5	MthMay
6	MthJun
7	MthJul
8	MthAug
9	MthSep
10	MthOct
11	MthNov
12	MthDec

The following formula returns number of current month. (Add any number of months past the current month to return a month in the future. Negative number for previous months)

=MONTH(TODAY())

The following formula would return the name of the required array one month after the current month. In this case MthMar.

=VLOOKUP(MONTH(TODAY())+1,MyTblArry,2,FALSE)

Now using Indirect you can insert that into the following formula in lieu of MthMar

=VLOOKUP($BR3,MthMar,5,FALSE)

=VLOOKUP($BR3,INDIRECT(VLOOKUP(MONTH(TODAY())+1,MyTblArry,2,FALSE)) ,5,FALSE)

---

Regards, OssieMac

Answer 2

Why not create a simple UDF. Like:

Public Function IndirectFormula(FORMULA As String)

    IndirectFormula = Evaluate(FORMULA)

End Function

1. Press Alt+F11;
2. RightClick your File Name;
3. Insert a module in your file;
4. Copy and Paste the above code.

Now use the =IndirectFormula(<Your Cell>) and the text in Your Cell would result into a formula result.

---

Best Regards, Faraz A Qureshi

Answer 3

Hi OssieMac:

Wow!  Thanks for your detailed and fascinating reply.  I am in the middle of my monthly accounting close so I may not get to test this for a day or two; I'll post back here my results!

Thanks again.  Really appreciate your time.

Mike

Answer 4

Hi Faraz:

If you get this reply:  sorry but I am getting a #REF! error (just like I do with the INDIRECT function).

Any suggestions?

Mike

Answer 5

Hi Faraz:

Thanks very much for the suggestion; I will try it . . . I figured a VBA solution would be the way to go.  There doesn't seem to be a formula-only solution to this problem given the limitations of the INDIRECT function (does not work on closed files across the network).

Thanks again.

Mike