Can I create a link as a variable?

[steinfm]

I have link to a file that is based on the month. Each month I have to change that link to the current month. Is there a way to do it automatically using a formula? In the link below, I would change the November to December.

'Y:\Rollforward\FY2010\11 November 2010\DOWNLOADS\[November Entries.xls]DATA'!$J$2

Can anyone help?

[NBVC]

Unfortunately for referencing outside sources this way, you need the INDIRECT function but it only works with open source workbooks:

Please Login or Register  to view this content.

Where A1 contains the Nov 11, 2010 date.

You can however download a free addin from here: Morefunc and employ INDIRECT.EXT function and it will work with closed workbooks:

Please Login or Register  to view this content.

The addin has a feature that allows you to embed the addin into the workbook so that you can share it without having to install to everybody's PC.

[steinfm]

I am using the indirect.ext formula as you mentioned, but I was wondering if I can use it in a formula as well? I'm trying to use it in a lookup formula and it's not working. I'm not sure if it's a syntax issue or just I'm trying to do something impossible. Here's the formula:

=+VLOOKUP(DAY($A$21),'[JANUARY 2011 WORKPAPERS.xls]BALANCING'!$A$11:$J$41,8,FALSE)

I am trying to use make "JANUARY 2011" variable, so next month it should look in the "FEBRUARY 2011" file.

Thanks.

[NBVC]

Something like:

=VLOOKUP(DAY($A$21),INDIRECT.EXT("'C:\mydocs\["&TEXT(A1,"mmmm yyyy")&" WORKPAPERS.xls]BALANCING'!$A$11:$J$41"),8,FALSE)

subbing your path in and the location of Jan 2010 (currently A1)