Variable Folder referencing in a cell formula.

[CoolioDude]

I have a problem trying to have the various data cell change the folder reference on a monthly basis after the month-end reports are complete. The excel file name reference is constant and does not change except for the folder reference e.g. 'T:\Finance\Finstmts\MB\P05-MAY\[MBSCHED.xls]Sched 1'!$F$16. The folder "P05-MAY" needs to be variable and refers to a vlookup reference that when I change a cell reference to the month e.g. 6 then the folder reference should change to "P06-Jun" but the Cell reference remains the same.

This is the code that I typed but it gives me a "#REF!" in the cell: ='T:\Finance\finstmts\MB\C2011\" & vlookup(C1,Q2:R13,2,FALSE) & "\[MBsched.xls]Sched 1'!$F$16.

Does anyone have any clue how I could get this to work.

 

[Joe4]

Off the top of my head, it looks like you are missing double quotes at the very beginning of your first part, and at the very end of your last part of your formula:

="'T:\Finance\finstmts\MB\C2011\" & vlookup(C1,Q2:R13,2,FALSE) & "\[MBsched.xls]Sched 1'!$F$16"

 

[AlphaFrog]

You could use the Find-Replace feature (Ctrl+H) to replace in the formulas all PO5-MAY with P06-JUN. That's not exactly what you asked for but it's easy enough to do.

 

[CoolioDude]

Off the top of my head, it looks like you are missing double quotes at the very beginning of your first part, and at the very end of your last part of your formula:

="'T:\Finance\finstmts\MB\C2011\" & vlookup(C1,Q2:R13,2,FALSE) & "\[MBsched.xls]Sched 1'!$F$16"

I have already tried that but it only give it to me as a text and not the data from the file that I am looking for. Is their another way of retriving data using this method?

 

[Joe4]

Take a look at the INDIRECT.EXT function. Here is an explanation of it: http://xcell05.free.fr/morefunc/english/indirect.ext.htm

Here is an example on MrExcel:
http://www.mrexcel.com/forum/showthread.php?t=553803

Here is another thread that has the path to download the free Add-In that has it: http://en.allexperts.com/q/Excel-1059/2009/2/Pull-data-antoher-spreadsheet.htm