So my problem is quite hard to explain, but I'll do my best.

I have a sheet that has dates as column headers. Each date represents a daily report that is produced. At the moment I have a load of VLOOKUPs going off to those daily report sheets to get data for this summary report.

What I'd like to do is make the VLOOKUP more dynamic. Instead of implicitly defining the name of the sheet in the =VLOOKUP prompt, I would like to get the Table_Array part of the VLOOKUP as the contents of the column header.

At the moment I have this...

=IF(ISERROR(VLOOKUP($A$1,{PATH}[031005.xls]tblFinal Productivity Report'!$1:$65536,16,FALSE)),0,VLOOKUP($A$1,'{PATH }[031005.xls]tblFinal Productivity Report'!$1:$65536,16,FALSE))

What I'd like to do is alter it so that the [031005.xls] bit is taken from another cell so that the formulas can be generic across the whole of the sheet and not have to be reproduced once a month.

Now I know of INDIRECT, but this only works on an open sheet, which I won;t have all of the dates open for.

So the question is, is there any way for me to make the VLOOKUP dynamic for the Table_Array, based on the contents of another cell on that sheet.

I hope that this makes sense.

TIA.

Ran into this almost same thing myself at one point. The quick and dirty way is to give the files a generic name so you never have to change the text in the formula. Then save a copy in an archive folder as whatever you want. Its the easiest way to avoid changing the formula, but it requires you to save to copies of the file now also. Still faster I think...at least it was for me. I searched for quite some time on a dynamic way to alter formulas...good luck. I would suggest ANYTHING written by John Walkenbach(Mr. Spreadsheet.)

Probably Excel Formulas 2003 would be the best bet.